### "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:

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

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

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

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

ReplyDeleteMark

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

ReplyDelete