Friday, February 1, 2013

EXPOSED! Excel's secret formula

There is a function that is not listed in the Insert Function dialog box. Microsoft does acknowledge the formula if you search their support page where you will find some nice examples for it. It's a simple function to calculate the days, months or years between two dates.

=DATEDIF(Date1,Date2,Interval)
  • Date1 is the first date (start date). Must be <= Date2 or a #NUM! error will be returned.
  • Date2 is the second date (end date). 
  • Interval is the unit of time for the result. Must be in the proper unit (see table) and enclosed in “quotes."
Unit Returns
"Y" The number of complete years in the period.
"M" The number of complete months in the period.
"D" The number of days in the period.
"MD" The difference between the days in start_date and end_date. The months and years of the dates are ignored.
"YM" The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD" The difference between the days of start_date and end_date. The years of the dates are ignored.

Try it out for yourself. Here's a sample you can try.