Tag: magic

Midyear Roundup: My Best Dates

Some of my fave Excel Date Functions. Some old, some new; all designed to help track the passage of time.

Date Functions:

EOMONTH
returns the last day of the month		 EOMONTH ( start_date , month )
EDATE
returns the date x number of months before/after a given date		  EDATE ( start_date , months )
NETWORKDAYS
calculates the number of working days
(see also NETWORKDAYS.INTL)		  NETWORKDAYS ( start_date , end_date , [holidays] )
TODAY
returns the current date		  TODAY( )
WEEKNUM
returns serial number for a given week		  WEEKNUM ( date , [week_start] )
WORKDAY
calculates the next valid work day
(see also WORKDAY.INTL)		 WORKDAY( start_date, days, [holidays] )

 
YEARFRAC
returns the date as a fraction of the year		  YEARFRAC ( start_date , end_date , [basis])

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

Hidden Excel: Find Date Differences with DATEDIF

In honor of the 40th anniversary of J.K. Rowling’s seminal classic, Harry Potter, this post magically exposes a hidden excel function!

Date Difference between J.K. Rowling’s Birthday and Harry Potter Movie Debut

Ever wonder if your spreadsheet is keeping something from you?

DATEDIF is an under-documented function. It allows  you to calculate the difference between two dates. Although it doesn’t appear in the Function Wizard (it exists to insure backward compatibility with Lotus and earlier Excel versions) it is a powerhouse when it comes to calculating date differences.

Using DATEDIF function:

  • Format: =DATEDIF ( date1, date2, “interval”)
  • date1 must be the earlier of the two dates or an #NUM error is returned.

Interval Arguments:

y Years
m Months
d Days
ym Months, years excluded
yd Days, years excluded
md Days, years and months excluded

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

%d bloggers like this: