Excel Length of Service Formula: Calculate Years and Months

Formulas & functions · Updated November 18, 2026

If the start date is in B2 and the date to measure service through is in C2, use =DATEDIF(B2,C2,"y")&" years, "&DATEDIF(B2,C2,"ym")&" months". For a start date of January 15, 2020, and an end date of November 2, 2026, the result is 6 years, 9 months.

What the length-of-service formula calculates

The formula reports completed calendar years and then the additional complete months after those years. It does not round up a partial month. In the example, six full anniversaries have passed by November 2, 2026, and nine additional full months have passed since the January 15 anniversary. There are also 18 extra days, which the formula intentionally leaves out.

Excel’s DATEDIF function calculates the difference between two dates. Its first argument is the earlier date, its second is the later date, and its third specifies the unit to return. The formula combines two units: "y" for complete years and "ym" for remaining complete months after full years are removed. The ampersands join the numbers and labels into one readable text result.

Length of service is not a special Excel data type: the result is text, not a date or a numeric value. That makes it convenient for a report or certificate, but less suitable for further calculations. Keep the original dates in separate cells so you can calculate service again, sort by start date, or apply a different reporting rule later.

Set up the dates and enter the formula

Use one row per person, with a genuine Excel date in each date column. For example, put an employee’s start date in B2 and the date through which service should be calculated in C2. Format those cells as dates if needed, then enter the formula in D2. Copy it down the column to calculate other rows.

  1. Enter the start date and as-of date in separate cells. Use an as-of date that matches the report you are preparing.
  2. Enter the years-and-months formula in the result column. Excel’s formula bar and cell should show the calculated text.
  3. Check a known example and confirm that the start date is earlier than or equal to the end date.
  4. For more employees, use the fill handle or copy and paste the formula into the remaining rows. Relative references adjust for each row.

For the example above, the cells contain actual dates—not text that merely looks like dates—and the formula returns 6 years, 9 months. In a real workbook, you can display dates in a readable format such as 15-Jan-2020; the cell’s display format does not change the underlying date used in the calculation.

Choose a fixed date or calculate through today

A fixed as-of date is usually best for a report that needs to be reproducible. If you recalculate the workbook next month, a fixed date keeps the original report’s result unchanged. For ongoing tracking, you can instead have Excel use the current date. With the start date in B2, use =DATEDIF(B2,TODAY(),"y") to return completed years as of today. The result changes when Excel recalculates the workbook.

TODAY() returns the current date, not the current time. Because it is a volatile function, Excel can update its result when the workbook recalculates. That is useful for a live service tracker, but it can make an old report appear to change. For an audit trail or a point-in-time snapshot, record the chosen as-of date in a cell and use that cell as the end date instead.

If you need completed years and months using today’s date, you can substitute TODAY() for C2 in the original formula. Keep in mind that the output will update over time. If you want the exact date used in a report to remain visible, store that date in C2 rather than embedding a moving date in every formula.

Understand the DATEDIF units

The unit argument determines what kind of difference Excel returns. These are the most useful units for service calculations:

  • "y" returns the number of complete years between the dates.
  • "m" returns the number of complete months across the entire interval, without splitting the answer into years.
  • "ym" returns the remaining complete months after complete years are excluded.
  • "d" returns the total number of days between the dates.

For a total-month figure, with the start date in B2 and the as-of date in C2, use =DATEDIF(B2,C2,"m"). The January 15, 2020, to November 2, 2026, example returns 81 complete months. This can help when a report needs a month count rather than a years-and-months label.

Excel also accepts a "md" unit, but Microsoft cautions that it can produce inaccurate results in some situations. For that reason, avoid depending on it for a precise remaining-days component. If a report needs days, decide what day-count rule it requires and calculate or verify that component using an appropriate method. Different reporting policies may treat end dates and anniversaries differently.

Make the formula safer for blank or invalid rows

A blank date or an end date earlier than the start date can lead to an unhelpful result or an error. In a list that includes incomplete records, use a guard so the result stays blank until both dates are present and in the right order. With the start date in B2 and the as-of date in C2, use:

=IF(OR(B2="",C2="",C2<B2),"",DATEDIF(B2,C2,"y")&" years, "&DATEDIF(B2,C2,"ym")&" months")

The formula returns an empty string if either date is blank or if the as-of date precedes the start date. Otherwise, it calculates the same years-and-months result. In Excel, the comparison operator in the formula is the less-than sign; the HTML above displays it as &lt; only because the formula is shown inside an HTML article. Enter the normal < character in Excel.

This check does not turn text into a valid date. If a cell contains text such as “unknown,” it is not a usable date value. Correct the underlying data, and consider using Data Validation to limit entries to dates if people will type into the workbook. Also check that regional date settings have not interpreted an entry such as 04/05/2026 differently than intended.

Decide which service measure fits the report

“Length of service” can mean different things in different contexts. Excel can calculate elapsed calendar time between two dates, but it cannot decide which dates an employer’s policy counts. Before choosing a formula, establish the reporting rule and use it consistently. Questions worth settling include:

  • Is service measured through today, the end of a month, or a specified reporting date?
  • Should the result show completed years only, years and months, total months, or days?
  • Does a rehire date replace the original start date, or should separate periods be recorded and combined?
  • Do approved leaves, part-time service, or other breaks change the credited service period under the applicable policy?
  • Should a service anniversary on a leap day follow a special rule?

For ordinary elapsed-date reporting, a start date and end date are enough. For credited service that excludes breaks, one start and end date may not be enough: each qualifying period may need its own dates and a separate calculation. Do not assume a calendar formula determines eligibility for a benefit or satisfies a workplace rule. Confirm the relevant policy before using a spreadsheet result for an official decision.

Common problems and troubleshooting

#NUM!: The start date is later than the end date, or one of the date values is otherwise invalid. Check the order of the arguments and inspect both cells. DATEDIF expects the earlier date first.

A strange or incorrect answer: One or both entries may be text rather than Excel dates, or a date may have been interpreted using a different regional order. Select the cell and check its value and number format. Changing a text cell’s display format alone will not necessarily convert the text into a real date.

The result is a number when you expected a label: The formula may calculate a numeric unit such as complete years or total months. To show a sentence-style result, join the numeric results with text using &, as in the opening formula.

A partial month seems to disappear: That is expected. The "ym" unit counts complete leftover months, not a rounded fraction. If a person has nine complete extra months and several days, the years-and-months result still reports nine months.

The answer changes unexpectedly: If the formula uses TODAY(), the as-of date moves as Excel recalculates. Use a date entered in a cell for a fixed report, and check whether calculation is set to automatic or manual if updates seem delayed.

Alternatives and presentation choices

For a simple report, a text result such as “6 years, 9 months” is often easiest to read. For sorting, charting, or further calculations, store numeric values separately instead. With the start date in B2 and the as-of date in C2, use =DATEDIF(B2,C2,"y") in one column for completed years, and use =DATEDIF(B2,C2,"m") in another for total completed months. The example returns 6 years and 81 months, respectively.

Another option is a decimal-year estimate, but that is not the same as completed service years. A decimal result depends on the day-count convention used, so it may not match a policy based on anniversaries. Likewise, rounding up a partial year can overstate completed service. Use decimal years only when the report specifically calls for that measure and its convention is understood.

Keep raw dates and calculation outputs in separate columns, label the as-of date clearly, and avoid overwriting source dates with formatted text. If you share the workbook, state whether the output is completed calendar service or a policy-defined credited-service figure. That small note can prevent readers from treating two different measures as interchangeable.

Frequently Asked Questions

What is the Excel formula for years of service?
With a start date in B2 and an end date in C2, use =DATEDIF(B2,C2,"y") for completed years. To include remaining complete months, combine the "y" and "ym" units as shown at the start of this page.

How do I calculate years of service from a hire date to today?
With the hire date in B2, use =DATEDIF(B2,TODAY(),"y") for completed years through today. Use a fixed as-of date in a cell instead if you need a result that will not change when the workbook recalculates.

Does DATEDIF round up partial years or months?
No. It counts completed intervals for the units used here. A person who has not reached the next anniversary does not receive an extra completed year, and a partial remaining month is not rounded up.

Why does DATEDIF return an error?
Check that both arguments are valid Excel dates and that the start date is not later than the end date. Blank cells and dates stored as text can also cause problems, so verify the underlying values rather than relying only on how they look.

Can Excel calculate service in days as well?
Yes. With the start date in B2 and the end date in C2, =DATEDIF(B2,C2,"d") returns total days between the dates. Confirm the reporting rule for inclusive or exclusive end dates before using that count for a formal purpose.

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.