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


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! 🙂


About MF

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”
    DAY($B$2)<DAY(A2))),1,0)&” years, “&MONTH($B$2)-MONTH(A2)+IF(AND(MONTH($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”


    • 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”


      • MF says:

        Thanks for your input, Igor.


        • 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) “


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


          • 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?


          • 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


            which I found quite insightful!



  2. Pingback: Calculate number of a specific day between two dates | wmfexcel

  3. Pingback: Calculating difference between dates in days

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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