DATEDIF practice exercises

Count the whole years or months between two dates, the way an age or years of service is counted.

Start the first exercise

How DATEDIF works

DATEDIF counts the whole years, months or days between two dates. It takes three arguments: the start date, the end date and a unit in quotes. "y" gives whole years, "m" whole months and "d" days. It only counts complete periods, which is why it fits an age or years of service.

With a hire date of Mar 15, 2018 in A2 and Nov 20, 2024 in B2, =DATEDIF(A2,B2,"y") returns 6 and =DATEDIF(A2,B2,"m") returns 80. The unit "ym" gives the months left over after the whole years, here 8. Join the pieces with & to get text like 6 years, 8 months: =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months".

The start date must come before the end date, or the formula returns #NUM!. Stay away from the unit "md": Microsoft warns that it can give wrong results. The exercises below cover an age in whole years, months of service and service as text with years and months.

  1. 1Work out an age with DATEDIFIn C2, use DATEDIF with the unit "y" to work out the age in whole years on the date in B2 of someone born on A2.
  2. 2Months of service with DATEDIFPremiumIn C2, use DATEDIF to count the whole months of service from the hire date in A2 to the date in B2.
  3. 3Years and months of service as textPremiumIn C2, show the service from A2 to B2 as "6 years, 8 months". Join DATEDIF with "y" for the whole years, the text " years, ", DATEDIF with "ym" for the months left over and " months".

Functions in this topic

Read the guide

Questions

Why isn't DATEDIF in Excel's function list?

DATEDIF is a legacy function that Excel keeps so older workbooks still work, so it does not show up in autocomplete or the Insert Function dialog. It works when you type the whole formula yourself, in current versions of Excel too. Just avoid the "md" unit, which can return wrong results.

How do I count the days between two dates in Excel?

Subtract the earlier date from the later one: =B2-A2. If the result shows up as a date, change the cell format to General or Number. DATEDIF(A2,B2,"d") gives the same answer. To count only working days, use NETWORKDAYS instead.

How do I get the years between two dates with decimals?

Use YEARFRAC: =YEARFRAC(A2,B2) returns the time between two dates as a fraction of a year, so 6 years and 8 months comes out near 6.7. Format the cell with two decimals if you want to see more. Use DATEDIF with "y" when you only want whole years.