Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Excel DATEDIF function

In Excel worksheet, if you want to calculate the number of years, months, days or weeks between two given dates, the DATEDIF function in Excel may help you. 


 Syntax:

The syntax for the DATEDIF function in Excel is:

=DATEDIF(Start_Date, End_Date, Unit)

 Arguments:

  • Start_Date, End_Date: The first and last dates to calculate the difference between.
  • Unit: The type of information you want to return.

Note: If the Start_date is greater than the End_date, the result will be #NUM! error value.

Unit Returns
“Y” The number of complete years between two dates.
“M” The number of complete months between two dates.
“D” The number of days between two dates.
“MD” The difference between the days. The months and years of the dates are ignored.
“YM” The difference between the days. The months and years of the dates are ignored.
“YD” The difference between the days. The years of the dates are ignored.

 Return:

Return the number of years, months, or days between two given dates.


 Examples:

Example 1: To get the number of years between two dates:

Enter or copy the below formula into a blank cell to get the number of complete years between two given dates.

=DATEDIF(A2,B2,"y")

doc datedif function 1


Example 2: To get the number of months between two dates:

Enter or copy the following formula, and you will get the number of complete months between two given dates.

=DATEDIF(A2,B2,"m")

doc datedif function 2


Example 3: To get the number of days between two dates:

Enter or copy this formula to get the number of complete days between two given dates.

=DATEDIF(A2,B2,"d")

doc datedif function 3


Example 4: To get the number of days between two dates ignoring years and months:

Enter or copy the below formula to get the number of days ignoring years and months between two given dates.

=DATEDIF(A2,B2,"md")

doc datedif function 4


Example 5: To get the number of days between two dates ignoring years only:

Enter or copy the following formula into a blank cell, and you will get the number of days excluding years only between two given dates.

=DATEDIF(A2,B2,"yd")

doc datedif function 5


Example 6: To get the number of months between two dates ignoring years and days:

Enter or copy this formula to return the number of months excluding years and days between two given dates.

=DATEDIF(A2,B2,"ym")

doc datedif function 6


Example 7: To get the number of weeks between two dates:

To return the number of weeks between two given dates, please use the following formula:

=(DATEDIF(A2,B2,"d"))/7

doc datedif function 7

Note: If you want to get the number of full weeks between the dates, please apply this formula:

=ROUNDDOWN((DATEDIF(A2, B2, "d") / 7), 0)

doc datedif function 8


Example 8: To get date difference in days, months and years between two dates:

Enter or copy the below formula to return the number of years, months and days between two given dates.

=DATEDIF(A2, B2, "y") &" years, "&DATEDIF(A2, B2, "ym") &" months, " &DATEDIF(A2, B2, "md") &" days"

doc datedif function 9

Note: In the above formulas, A2 is the start date, B2 is the end date, please change them to your need.


Example 9: To calculate the ages based on birth of date:

The DATEDIF function also can help you to calculate someone's age based on the date of birth, please enter this formula:

=DATEDIF(D2,TODAY(),"y") & " Years, " & DATEDIF(D2,TODAY(),"ym") & " Months, " & DATEDIF(D2,TODAY(),"md") & " Days"

doc datedif function 10

Note: D2 is the cell contains the birthdate, please change the cell reference to your need.

Tips: You can also replace the cell reference in the above formulas with the date text string, such as:

=DATEDIF("1/12/2013","5/23/2018","y")

 Relative Dateif articles:

How To Count / Calculate Weeks / Months / Years Between Two Dates In Excel?

How To Convert Birthdate To Age Quickly In Excel?


Excel Productivity Tools

300 Advanced Features Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 70% time.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Office Tab Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.