How to Calculate Years and Months of Service in One Excel Cell with DATEDIF in WPS Office
Question details
The user needs a DATEDIF formula that calculates complete years and remaining months from an employee's start date to today and combines the values into one readable cell.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains employee records and wants to display length of service in a single cell using a start date, for example D2.
- Observed behavior
- The goal state is to see a readable result such as "3 year(s) and 4 month(s)" in the selected cell.
Verify that start dates are entered as real date values rather than text. If the result cell is formatted as Text, switch it to General before entering the formula.
Use DATEDIF to Combine Years and Remaining Months in One Cell
Use this when you have a start date in a cell and need an automatically updated length of service sentence in one spreadsheet cell.
DATEDIF returns the complete elapsed units between two dates. The "y" unit returns complete years, and "ym" returns the remaining whole months after those complete years.
The formula joins both results with text so the final value reads naturally, for example "5 year(s) and 7 month(s)".
Open your workbook in WPS Office Spreadsheets and click the cell where the service length should appear.
Type =DATEDIF(D2,TODAY(),"y")&" year(s) and "&DATEDIF(D2,TODAY(),"ym")&" month(s)" and press Enter.
Drag the fill handle down from the result cell to copy the formula to other start date rows in the same column.
Calculate Service Length in One Cell with WPS Spreadsheets
WPS Office Spreadsheets supports DATEDIF, so you can create the same years-and-months service formula in a free, familiar spreadsheet interface and keep working with your existing Excel files.
- 1. Download WPS Office: Go to the official WPS Office download page and install WPS Office on your computer.
- 2. Open the employee workbook: Open your existing .xlsx or .xls file in WPS Office Spreadsheets.
- 3. Enter the DATEDIF service formula: Click the result cell, type =DATEDIF(D2,TODAY(),"y")&" year(s) and "&DATEDIF(D2,TODAY(),"ym")&" month(s)", and press Enter.
- 4. Apply the formula to other rows: Drag the fill handle down the column to calculate service length for other employees.
Frequently Asked Questions
Why does my DATEDIF formula return #NUM!?
This usually means the start date is later than TODAY() or the cell does not contain a valid date. Check that D2 is formatted as a date and is earlier than the current date.
Can I change the wording "year(s) and month(s)"?
Yes. Edit the text inside the quotation marks in the formula, for example change " year(s) and " to " years, ".
Does DATEDIF work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports the DATEDIF function, so you can use the same English formula shown here.
How can I show only total months of service?
Use =DATEDIF(D2,TODAY(),"m") in an empty cell to return the total number of complete months between the start date and today.
What if my dates are stored as text?
Select the date cells and set the number format to Date, or re-enter the dates. You can also use DATE(year,month,day) to create a valid date value.
