If the hire date is in A2 and the end date is in B2, use =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months" to show completed years and the remaining whole months of service. Enter real Excel dates in both cells; the result is text, such as “4 years, 7 months.”
What the Service Formula Calculates
The formula uses Excel’s DATEDIF function twice. The first calculation returns the number of complete years between the two dates. The second returns the number of complete months left after those full years are accounted for. Combining the results with the ampersand operator produces one readable label.
For example, if A2 contains 15 March 2019 and B2 contains 2 November 2023, the elapsed time is four complete years and seven additional complete months. The remaining days are not displayed. They do not round the month count up: an interval of four years, seven months, and 30 days still displays as four years and seven months unless a full additional month has elapsed.
This is an anniversary-based duration, not a count of calendar-year labels. Someone hired late in December does not have a completed year of service on the following 1 January. The year is complete on the applicable anniversary date. That distinction matters for reports, eligibility tracking, and any policy that defines service by a particular rule.
How to Set Up the Date Cells
Put a start date in A2 and the date through which you want to measure service in B2. The second date might be a termination date, a reporting date, or a manually entered “as of” date. Put the formula in another cell, such as C2. If you have several employees, use the same columns for every row and fill the formula down.
- Use actual Excel dates, not date-looking text. A cell that displays 3/15/2019 might still contain text, depending on how it was imported.
- Format the input cells as dates if the displayed values are confusing. On Windows, select the cells and use Home > Number Format; the same number-format choices are available in Excel for Mac, although the ribbon layout can differ.
- Keep the start date earlier than or equal to the end date. A reversed date range causes DATEDIF to return an error.
- Choose one consistent reporting date for a workforce report. Using different end dates for different rows can make comparisons misleading.
The formula syntax is the same in Excel for Microsoft 365 on Windows and Excel for Mac. Excel for the web also supports the calculation. In some regional settings, Excel uses a semicolon rather than a comma as the argument separator; use the separator configured for your installation if the comma-separated formula is rejected.
Calculating Service Through Today
For a current service calculation, use TODAY as the end date rather than entering a date that must be updated manually. With hire dates in A2 and the result in B2, use =DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months". The result updates when Excel recalculates the workbook on a later date.
TODAY returns the current date without a time. It does not permanently record the date on which the workbook was opened or calculated. That is useful for a live roster, but not necessarily for a historical report that must remain fixed. For an audit or past snapshot, enter the reporting date in a cell and reference it as the end date instead. You can also copy a live result and use Paste Special > Values to preserve the displayed result, but keeping the original input dates and reporting date is generally clearer.
A blank hire-date cell should not be treated as a valid start date. If your list contains blank rows, add a blank check so the formula does not display an unintended duration. With the hire date in A2 and the end date in B2, use =IF(OR(A2="",B2=""),"",DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months"). This leaves the result blank until both dates are entered. It does not replace checking that the dates are in the correct order.
Keeping Years and Months as Numbers
The combined display formula returns text because it joins numbers with words. That is convenient for a report, but text is awkward for sorting, averaging, filtering by a numeric threshold, or adding durations. If you need to analyze the values, place the year and month calculations in separate cells instead.
With the start date in A2 and end date in B2, put the complete-year result in C2 using =DATEDIF(A2,B2,"y"). With the same date layout, put the leftover-month result in D2 using =DATEDIF(A2,B2,"ym"). For the example from 15 March 2019 to 2 November 2023, C2 returns 4 and D2 returns 7.
These two values are useful as separate fields, but do not add them together: seven months is not seven additional years. If you want a single approximate number of years for a chart, decide how to represent partial years and document that choice. Dividing months by 12 is an approximation, while calculating a fraction based on days uses a different convention. Neither should be substituted for the years-and-months result without considering what the report is meant to communicate.
Worked Examples and Interpreting the Result
Suppose the hire date is 10 June 2020 and the end date is 9 June 2024. The employee has not yet reached the fourth anniversary, so the result is 3 years, 11 months. On 10 June 2024, it becomes 4 years, 0 months. The one-day difference illustrates why a completed-service formula is not the same as subtracting the year numbers displayed on the calendar.
For a shorter interval, suppose the dates are 5 January 2024 and 20 April 2024. The result is 0 years, 3 months. The extra 15 days do not make a fourth complete month. If the end date moves to 5 May 2024, the result becomes 0 years, 4 months.
Zero values are meaningful. A new employee can have zero completed years and several completed months; an employee on an anniversary can have a positive number of years and zero remaining months. The basic formula displays both values, including “0 years” or “0 months,” which is often helpful in consistent reports. If you prefer singular wording such as “1 year,” you can build a more elaborate formula with conditional text, but the basic version is easier to audit and maintain.
Common Errors and Date Edge Cases
A #NUM! error commonly indicates that the start date is later than the end date. Check for swapped columns, an incorrect date, or an end date that was entered before the employee’s hire date. A result that looks unusually large can mean a blank start date has been interpreted as a date near the beginning of Excel’s date system, or that imported date text was interpreted incorrectly.
If Excel rejects a date or returns an unexpected result, inspect the underlying values. Applying a date format changes how a numeric date is displayed, but does not convert arbitrary text into a valid date in every case. Re-enter the value in a recognized format or convert the source data before calculating. Also check whether day and month were reversed during import; 4/5/2023 can mean different dates in different regional conventions.
Month-end and leap-day dates deserve attention when service is close to a boundary. A person hired on the last day of a month, or on 29 February, may have a policy-defined anniversary that differs from a simple expectation about the calendar. DATEDIF reports elapsed completed years and months according to Excel’s date calculation; it cannot determine an employer’s contractual or legal rule. Test dates around the relevant anniversary and confirm which date the governing policy recognizes.
Avoid using DATEDIF’s “md” unit to report the leftover days as a general fix. Its behavior can be surprising in certain date combinations, and days are not needed for a years-and-months display. If a report also needs days, define the required convention and calculate or explain that component separately.
Choose a Service Definition Before Reporting
For a simple roster, completed years plus remaining whole months is usually an intuitive description of elapsed time. But “years of service” can mean different things in an organization. A benefits plan, seniority policy, contract, or official record may specify which dates count, how breaks in service are treated, or whether a particular day is included. A spreadsheet formula calculates dates; it does not decide those rules.
- Use a fixed end date for a report covering a past period, and make that date visible in the sheet.
- Use TODAY for a live view that should advance automatically.
- Confirm the handling of rehires, leaves, part-time service, and breaks if the result is meant to represent credited service rather than continuous time since hire.
- Check near-anniversary cases and month-end or leap-day hires against the rule you must follow.
- Label the result clearly, for example “elapsed service as of 30 June,” rather than implying that it includes adjustments the formula does not make.
This distinction prevents a common misconception: a mathematically correct elapsed duration is not automatically an official service-credit determination. For payroll, benefits, or compliance decisions, follow the applicable documented policy and ask the responsible professional when its terms are unclear.
Frequently Asked Questions
Does DATEDIF work in Excel for Microsoft 365?
Yes. DATEDIF is available for compatibility in current Excel, including Microsoft 365, though it may not appear in formula autocomplete. Enter the function and its arguments directly. The “y” and “ym” units are the ones used here for complete years and remaining complete months.
How do I calculate years and months from a hire date to today?
Use TODAY() as the end date in the years-and-months formula. The answer updates with the current date when Excel recalculates. Use a cell containing a fixed reporting date instead if you need the result to represent a past snapshot.
Why does the result show zero years?
Zero means fewer than one complete anniversary year has elapsed. The month portion can still show completed months, so a result such as 0 years, 8 months is expected for someone with eight months of service.
Can the result include days too?
Yes, but first decide whether you need elapsed days or a policy-specific inclusive count. The years-and-months formula intentionally omits leftover days. Avoid assuming that adding one day or using a day unit in DATEDIF will match a formal service rule; check boundary cases against the required definition.
Can I sort the combined years-and-months result?
Not reliably as text. A label such as “10 years, 1 months” may sort alphabetically rather than by duration. Keep completed years and remaining months in separate numeric columns for sorting and analysis, and create the text label only for display.
Related
- Data Cleaning Tools in Excel: What to Use and When
- Data Cleaning in Excel: A Practical Example
- Data Cleaning Shortcuts in Excel: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Week of the Year: WEEKNUM vs. ISO WeeksTo get a week number from a date in Excel, use =WEEKNUM(A2,2) for weeks that start on Monday,…
- Power Query Find Text in a String: M Functions and ExamplesTo find whether a Power Query text value contains a word or phrase, use Text.Contains. In a Custom…
- Excel Joining Two Text Columns: Formulas and ExamplesTo join two text columns in Excel, enter a formula such as =A2&" "&B2 in a third column,…
- Excel Let Text Spill Into the Next Cell? How It WorksYes. In Excel for Microsoft 365, text can appear to spill into the cell to its right when…
- Sort Excel by Color: Cells, Font Colors, and IconsTo sort Excel rows by color in Microsoft 365 for Windows, select your data, choose Data > Sort,…
- Excel Not Greater Than: How to Use the <= Operator in FormulasIn Excel, “not greater than” means less than or equal to, and the operator is <=. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.