Excel Length of Service in Years and Months: Formulas

Formulas & functions · Updated November 18, 2026

To calculate completed length of service in years and remaining months, enter the start date in A2 and the end date in B2, then use =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months". For example, a start date of June 15, 2018, and an end date of November 2, 2026, returns 8 years, 4 months.

How the years-and-months calculation works

The result above reports whole, completed calendar years and then whole months left over after those years. It does not round up based on the number of days in the remaining part of a month. In the example, eight full anniversaries have passed by November 2, 2026; after the June 15 anniversary in 2026, four complete months have passed, but a fifth has not.

Excel’s DATEDIF function calculates the difference between two dates. Its first argument is the start date, its second is the end date, and its third specifies the unit to return. The unit "y" returns completed years. The unit "ym" returns completed months after subtracting complete years. Joining those two results with text produces a reader-friendly label.

This is generally the useful interpretation for employee tenure, membership duration, and similar service calculations: an anniversary or monthly milestone counts only once it has actually been reached. It is different from dividing a day count by 365 or 30, which can produce approximate values and may not align with calendar anniversaries.

Set up the dates and enter the formula

In a simple worksheet, use one column for the person or account, one for the start date, one for the end date, and one for the calculated service period. For example, put a name in A2, the start date in B2, the end date in C2, and the result in D2. Enter actual Excel dates in the date cells, not date-looking text.

  1. Type a clear header such as Start date, End date, or Service period in row 1.
  2. Enter the start and end dates in the same row. An end date must be on or after its corresponding start date.
  3. With start dates in B2:B100 and end dates in C2:C100, enter this formula in D2: =DATEDIF(B2,C2,"y")&" years, "&DATEDIF(B2,C2,"ym")&" months".
  4. Copy D2 down the column using the fill handle, or select the formula cell and press Ctrl+C, select the destination cells, and press Ctrl+V. On Mac, use Command+C and Command+V.

The copied formula changes its row references automatically, so D3 uses B3 and C3. If Excel displays a number instead of a date in the input cells, apply a date format from Home > Number Format. Formatting changes how a value looks; it does not turn arbitrary text into a valid date.

Calculate service through today

If the service period should run from a start date up to the current date, Excel’s TODAY function supplies the end date. With each person’s start date in A2 and the result formula in B2, use:

With start dates in A2:A100 and the result in B2, this formula returns the current completed service period: =DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months".

TODAY() updates when the workbook recalculates, so the displayed service period can change as calendar milestones pass. It is useful for a live roster, but not always suitable for a report that must preserve a fixed calculation date. For a dated snapshot, enter the reporting date in a cell and refer to that cell as the end date instead. This makes it possible to reproduce the same report later.

For example, put the reporting date in B1, each start date in A2:A100, and the result in C2. Use =DATEDIF(A2,$B$1,"y")&" years, "&DATEDIF(A2,$B$1,"ym")&" months" in C2 and fill down. The dollar signs keep the reporting-date reference fixed as the formula is copied.

Keep the result numeric when you need to analyze it

A combined result such as 8 years, 4 months is text. That makes it easy to read, but text is not ideal for sorting durations numerically, charting them, or calculating averages. If you expect to analyze service periods, put years and remaining months in separate numeric columns and create a display label only where needed.

With the start date in A2 and end date in B2, enter this in C2 to return completed years: =DATEDIF(A2,B2,"y"). With the same date layout, enter this in D2 to return the remaining months: =DATEDIF(A2,B2,"ym"). For the June 15, 2018, to November 2, 2026, example, C2 returns 8 and D2 returns 4.

You can also calculate the total number of complete months, which is often easier for sorting or calculations. With the start date in A2 and end date in B2, use =DATEDIF(A2,B2,"m"). The example returns 100 complete months: 8 × 12 + 4. To split that total back into years and months, use =QUOTIENT(DATEDIF(A2,B2,"m"),12) for years and =MOD(DATEDIF(A2,B2,"m"),12) for remaining months. These return 8 and 4, respectively.

Keeping numeric results separate also makes it clearer what a report is measuring. For instance, 8 years and 11 months is nearly nine years, but the completed-years column correctly remains 8 until the next anniversary. Avoid averaging a text label or treating the years and months columns as if both were decimal fractions of a year.

Handle blanks and invalid date ranges

A plain DATEDIF formula can show an error or an unwanted result when a row is incomplete. For a current-date calculation, it is common to leave the output blank until the start date has been entered. With a start date in A2 and the result in B2, use:

=IF(A2="","",DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months")

This checks whether A2 is empty before calculating. If you use a separate end-date column, check both date cells. With the start date in A2, the end date in B2, and the result in C2, use =IF(OR(A2="",B2=""),"",DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months"). Once both dates are present, Excel calculates the result normally.

If the end date is earlier than the start date, DATEDIF returns the #NUM! error. Check whether the dates were reversed or mistyped rather than hiding the error without investigating it. If a date was imported from another system, it may be stored as text. A date that looks like 6/15/2018 can still fail to behave as a date if Excel did not recognize it; re-enter it as a date or convert the source data before calculating.

Choose a service-period convention carefully

“Length of service” can mean different things in different reports. The formula gives completed calendar years and months between two dates. It does not decide which date counts as the start of service, how breaks in service should be treated, or what to do with a future end date. Those are rules for the dataset or organization, not Excel’s date arithmetic.

  • Active service: Use TODAY for a live calculation, or a fixed reporting date for a reproducible snapshot.
  • Ended service: Use the actual end date in the row, and verify that it follows the start date.
  • Inclusive day counts: Years and months usually describe elapsed time between dates; they do not count both endpoints as full days. If a policy requires an inclusive day count, establish that rule separately.
  • Partial months: DATEDIF reports completed months, not fractions of a month. If a policy rounds partial months up or uses a special cutoff, implement that rule explicitly and validate it against approved examples.

This distinction matters when a result is used in a formal HR, benefits, contract, or eligibility process. Treat the spreadsheet output as a date calculation, then check the applicable policy and definitions before relying on it for a decision.

Common DATEDIF questions and pitfalls

DATEDIF is supported in Excel for Microsoft 365 on Windows, but it may not appear in the function autocomplete list while you type. Enter the function name and arguments directly, and check that the parentheses and quotation marks are balanced. The unit must be enclosed in straight double quotation marks, such as "ym", not curly typographic quotes.

Use "y" and "ym" together for the years-plus-remaining-months display. Using "m" returns total completed months, not the remainder after complete years. Using "d" returns days. These units answer different questions, so choose according to what the report needs to show.

Month lengths vary, and leap years add another complication to rough day-based methods. That is why =INT((B2-A2)/365) is not a reliable substitute for completed calendar years: it treats every year as 365 days and can misstate anniversary-based service. DATEDIF compares the calendar dates rather than estimating years from a fixed day count.

Excel for Mac also supports DATEDIF and the same formula syntax. In Excel for the web, the function is available as well; the same date-entry and blank-cell considerations apply. If formulas in a workbook use semicolons instead of commas, the regional list-separator setting may differ. Use the separator Excel expects in that installation, while keeping the function arguments and quoted unit intact.

Frequently Asked Questions

How do I calculate years and months between two dates in Excel?
Put the start date in A2 and the end date in B2, then use =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months". It returns completed years and the remaining completed months.

How do I calculate length of service from a start date to today?
With the start date in A2, use =DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months". The answer updates as Excel recalculates the workbook.

Why does DATEDIF return #NUM!?
The most common reason is that the end date is earlier than the start date. Check the order and contents of the date cells. Also confirm that imported values are real Excel dates rather than text.

Does DATEDIF round up partial months?
No. The "ym" unit returns whole completed months left after complete years. If only part of the next month has elapsed, that partial month is not counted.

Can I sort service periods from longest to shortest?
A combined years-and-months label is text, so it may sort alphabetically rather than by duration. Keep completed years and remaining months in separate numeric columns, or calculate total completed months for a straightforward numeric sort.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.