Calculate Completed Years and Months Between Dates with Excel DATEDIF
Question details
Calculate the number of completed years and remaining completed months between two dates by using the DATEDIF function.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to display an elapsed period such as years and months from a start date to an end date.
- Observed behavior
- DATEDIF can return an error or appear unavailable when the dates are entered in reverse order or when localized Excel uses different formula names or argument separators.
Enter real Excel dates, not date-looking text, in separate cells. DATEDIF requires the start date to be earlier than or equal to the end date, so check the date order before entering the formula.
Use DATEDIF with the earlier date as the start date
Use this method when your start date is known to be earlier than your end date and you need a readable completed-years-and-months result.
DATEDIF with the "y" unit returns full years, while the "ym" unit returns the remaining full months after those years are removed.
Type the earlier date in cell A1 and the later date in cell B1, for example 11/21/2022 in A1 and 1/17/2023 in B1.
Select the result cell and enter =DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months".
Press Enter. For 11/21/2022 through 1/17/2023, the result is 0 years 1 months because only one complete month has elapsed.
Make the DATEDIF formula work when the date order may be reversed
Use this option when either date may be entered first and you want to avoid a #NUM! error caused by a later start date.
Check localized Excel formula syntax
Use this method if DATEDIF is not accepted in a Swedish or other localized installation of Excel.
Calculate Completed Years and Months with DATEDIF in WPS Spreadsheets
WPS Spreadsheets lets you calculate elapsed, completed years and months with DATEDIF and combine the results into a clear text label. Enter the dates in cells, use the earlier date first, and apply a formula that remains easy to update when the dates change.
- 1. Open a spreadsheet: Open WPS Spreadsheets and enter the two dates in cells A1 and B1.
- 2. Choose a result cell: Click an empty cell where the completed years and months should be displayed.
- 3. Enter a date-order-safe formula: Type =DATEDIF(MIN(A1,B1),MAX(A1,B1),"y")&" years "&DATEDIF(MIN(A1,B1),MAX(A1,B1),"ym")&" months".
- 4. Calculate the result: Press Enter to display the completed years and remaining completed months.
Frequently Asked Questions
Why does DATEDIF return a #NUM! error?
DATEDIF returns #NUM! when its first date is later than its second date. Put the earlier date first, or use MIN(A1,B1) and MAX(A1,B1) inside the formula.
What is the difference between the "y" and "ym" DATEDIF units?
The "y" unit returns the number of full years between the dates. The "ym" unit returns the full months remaining after complete years have been excluded.
How do I calculate years and months if the dates are in either order?
Use =DATEDIF(MIN(A1,B1),MAX(A1,B1),"y")&" years "&DATEDIF(MIN(A1,B1),MAX(A1,B1),"ym")&" months" to automatically place the earlier date first.
Why is DATEDIF not recognized in my Swedish version of Excel?
Your localized Excel may require a translated function name or semicolons instead of commas. Check the formula separator in your regional settings or use Excel's Functions Translator add-in.
Does DATEDIF count partial months as full months?
No. DATEDIF reports completed periods only, so an unfinished month is not added to the "ym" result.
