"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.

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

Post a Comment

Popular posts from this blog

Keyboard Shortcut to add/delete rows or columns in Excel

Excel Keyboard Shortcuts - Handy during day to day work

Alternative for SUMIF and SUMIFS - Part 2 (SUMPRODUCT)