Friday, August 19, 2011

"DATEDIF" function to calculate the number of days/months/years between dates

A simple subtraction between two date values gives you the number of days between the two days. However, to actually calculate the exact number of days or number of completed months or number of completed years, Excel offers a hidden function called "DATEDIF".

Syntax: =datedif(date 1, date 2,criteria), where
date 1 is the start date or the older of the two dates
date 2 is the end date or the recent date
Criteria can be as follows:
  "d" - number of completed days between the two dates
  "m" - number of completed months between the two dates
  "y" - number of completed years between the two dates
  "yd" - number of completed days between dates ignoring the year i.e. considering them to be in the same year
 "ym" - number of completed months between dates ignoring the year i.e. considering them to be in the same year
 "md" - number of completed days between dates ignoring the year & month i.e. considering them to be in the same year and same month

Therefore if I want to calculate my age, I can write the formula as:

Date 1: 1-Jan-1980 (H4 cell)
Date 2: 19-Aug-2011 or today (I4 cell)
Formula: =DATEDIF(H4,I4,"y")&" years "&DATEDIF(H4,I4,"ym")&" months and "&DATEDIF(H4,I4,"md")&" days"
Result: 31 years 7 months and 18 days


Caution:
  • Date 1 ought to be lesser than or older than Date 2
  • of course, both dates should be valid date formats
Try it... its interesting!!

Thanks Charan for reminding me on this function, which I had stopped using.

2 comments:

  1. Hey, this is the exact same tip I got in the excel addict newsletter!! What gives?

    Mark

    ReplyDelete
  2. Ya, I too read that article but only after my post. This was merely coincidental!

    ReplyDelete