## How to calculate number of days, months, or years from one date to another date – DATEDIF function

Recently, I got enquired about function for calculating number of days from one date to another date.  DATEDIF is on the top of my mind.  ：）

Interestingly this function is not commonly known probably because it won’t show up in the formula bar, even you type exact the DATEDIF in the formula bar… I still don’t know why actually.

Anyway, this simple function is quite useful.

Here’s the Syntax

DATEDIF(start_date,end_date,unit)

where start_date and end_date are quite straight forward, right?

Still a reminder for you: “The end_date argument must be a date that occurs after start_date. The day of the ending date is not counted in the final result.” This is extracted from the HELP menu.

“Unit” may require a little description:

 “Y” – Number of FULL YEAR （from start date to end date) “YM” – Number of FULL MONTH regardless of Year “MD” – Number of DAY regardless of Year and Month “YD” – Number of DAY regardless of Year “M” – Number of FULL MONTH “D” – Number of DAY

Example gives better explanation: #NUM! error because start_date occurs before end_date OR incorrect Unit used.

Let’s try it on your own! 🙂 An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged . Bookmark the permalink.

### 9 Responses to How to calculate number of days, months, or years from one date to another date – DATEDIF function

1. Igor says:

I have a very good formula for this (All in One). First table A2 and B2 gives you this:
“3 years, 0 months, 0 days”
Formula:
=YEAR(\$B\$2)-YEAR(A2)-IF(OR(MONTH(\$B\$2)<MONTH(A2),AND(MONTH(\$B\$2)=MONTH(A2),
DAY(\$B\$2)<DAY(A2))),1,0)&” years, “&MONTH(\$B\$2)-MONTH(A2)+IF(AND(MONTH(\$B\$2)
<=MONTH(A2),DAY(\$B\$2)<DAY(A2)),11,IF(AND(MONTH(\$B\$2)<MONTH(A2),DAY(\$B\$2)

=DAY(A2)),12,IF(AND(MONTH(\$B\$2)>MONTH(A2),DAY(\$B\$2)<DAY(A2)),-1)))&” months,
“&\$B\$2-DATE(YEAR(\$B\$2),MONTH(\$B\$2)-IF(DAY(\$B\$2)<DAY(A2),1,0),DAY(A2))&” days”

Like

• Igor says:

for some reason formula posted with space.
I’ll try again:
=YEAR(\$B\$2)-YEAR(A2)-IF(OR(MONTH(\$B\$2)<MONTH(A2),AND(MONTH(\$B\$2)=MONTH(A2),DAY(\$B\$2)<DAY(A2))),1,0)&” years, “&MONTH(\$B\$2)-MONTH(A2)+IF(AND(MONTH(\$B\$2)<=MONTH(A2),DAY(\$B\$2)<DAY(A2)),11,IF(AND(MONTH(\$B\$2)<MONTH(A2),DAY(\$B\$2)>=DAY(A2)),12,IF(AND(MONTH(\$B\$2)>MONTH(A2),DAY(\$B\$2)<DAY(A2)),-1)))&” months,”&\$B\$2-DATE(YEAR(\$B\$2),MONTH(\$B\$2)-IF(DAY(\$B\$2)<DAY(A2),1,0),DAY(A2))&” days”

Like

• MF says:

Like

• Igor says:

You can basically make your formula more independent by combining all arguments into one formula like this:
=DATEDIF(A32,B32,”Y”)&” Year(s) ” & DATEDIF(A32,B32,”YM”) & ” Month(s) ” & DATEDIF(A32,B32,”MD”) & ” Day(s) “

Like

• MF says:

Hi Igor,
Yes, you are right. We can combine the formula and remove the helper columns. I did it step by step so that can be read and understood easier.
Cheers,

Like

• Igor says:

I actually like your formula better than mine. It’s shorter, cleaner and smarter:
=DATEDIF(A32,B32,”Y”)&” years ” & DATEDIF(A32,B32,”YM”) & ” months ” & DATEDIF(A32,B32,”MD”) & ” days”
The only problem I found in Excel 2010 when I type =datedif nothing comes up like with regular formulas until you put (, then Excel shows =DATEDIF(). Maybe that happens because its not available in Office 2010?

Like

• MF says:

Hi Igor,
That’s because the DATEDIF function in Excel is somehow an “unofficial” function. You won’t find it in the function library.
And IF you are “lucky” enough, you may encounter a bug when using DATEDIF.

You may be interesting to read the following by Microsoft MVP for Excel – Rick Rothstein

http://www.excelfox.com/forum/f22/recommendation-do-not-use-the-undocumented-datedif-function-321/

which I found quite insightful!

Cheers,

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.