Find Years of Service in Excel with a Simple Formula

To calculate completed years of service in Excel, put each employee’s hire date in B2 and enter =DATEDIF(B2,TODAY(),"y") in C2. The result is the number of full years between the hire date and today; fill the formula down to calculate the rest of the list. Use valid Excel dates in the hire-date column, not dates stored as text.

What You’ll Need

A Microsoft Excel workbook. These instructions are for Excel for Microsoft 365 on Windows; the formulas also work in Excel for Mac and Excel for the web.
A column of employee hire dates, with one date per row. The examples use column B, starting at B2.
An empty column for the service results. The examples put results in column C.
Optionally, a date to calculate service as of, such as a reporting date. This is useful when you need a consistent snapshot rather than a result that changes each day.

Step-by-Step

Set up the columns. Add a heading such as Hire Date in B1 and Years of Service in C1. Keep each employee’s hire date on the same row as their other details. If you have a name in A2, for example, that person’s hire date should be in B2 and their service result will appear in C2. Make sure B2 contains a date Excel recognizes, such as 6/15/2018, rather than a text entry that only looks like a date.
Enter the completed-years formula. With the hire date in B2 and the result going in C2, enter =DATEDIF(B2,TODAY(),"y") in C2 and press Enter. DATEDIF compares the start date with the end date, and the unit “y” asks for the number of whole years. For a hire date of June 15, 2018, and an as-of date of November 3, 2026, the result is 8: the employee has passed eight anniversaries, but not the ninth.
Check that the result means what you intend. This formula reports completed years, not a rounded estimate. Someone hired on November 4, 2018 would have 7 completed years on November 3, 2026, even though the dates are nearly eight years apart. That distinction matters for service awards, reporting bands, and any process based on anniversaries. Confirm the reporting rule your organization uses before relying on the number.
Fill the formula down the column. Select C2 and drag the small square at the lower-right corner of the selection down through the employee rows. Excel adjusts the relative reference for each row, so the next row refers to B3, then B4, and so on. You can also select C2 and the blank cells below it, then choose Home > Fill > Down. On Windows, Ctrl+D fills selected cells downward; on Mac, use the Fill Down command if your keyboard shortcut differs by keyboard setup.
Format the results as whole numbers. The formula returns a number, so a general number format is usually appropriate. If Excel displays a date or an unexpected decimal, select the result cells and choose Home > Number Format, then select General or Number and set decimal places to zero. Avoid using a date format for a years-of-service result.
Choose a fixed reporting date when needed. TODAY() updates when the workbook recalculates, so results can change tomorrow or when another person opens the file. To freeze the calculation at a reporting date, enter that date in E1 and label the cell As of date. With hire dates in B2:B100 and the reporting date in E1, enter =DATEDIF(B2,$E$1,"y") in C2, then fill down. The dollar signs keep the reference to E1 fixed as the formula is copied. If E1 is November 3, 2026 and B2 is June 15, 2018, the result is 8.
Make blank rows stay blank. If your employee list has empty hire-date cells, a basic calculation may return a misleading number or an error. With the hire date in B2 and the result in C2, use =IF(B2="","",DATEDIF(B2,TODAY(),"y")). The IF check leaves C2 empty when B2 is empty; otherwise, it calculates completed years. Fill this version down the result column instead of the basic formula.
Flag future dates rather than calculating them. A future hire date is usually a data-entry mistake or a planned start date, and DATEDIF can return an error when its start date is later than its end date. With the hire date in B2 and the result in C2, enter =IF(B2="","",IF(B2>TODAY(),"Check date",DATEDIF(B2,TODAY(),"y"))). This leaves missing dates blank, displays Check date for a future date, and calculates years for dates through today. The result column now contains both numbers and text, so treat it accordingly in later calculations.
Save and check a few rows. Compare a recent hire, a long-serving employee, and a blank or future date against the source records. Confirm that the formula references the correct hire-date column and that the result reflects completed anniversaries. If the workbook is a formal report, note the as-of date in the sheet so readers know when the calculation was made.

Common Mistakes

Using subtraction and dividing by 365. A formula such as (today minus hire date) divided by 365 is not a reliable way to count completed years. Calendar years do not all contain 365 days, and the result may be a decimal that gets rounded incorrectly. DATEDIF with the “y” unit counts full years based on anniversaries.
Confusing completed years with rounded years. DATEDIF does not round to the nearest year. It counts only anniversaries that have already passed. If a policy calls for a different measure, such as a fraction of a year, confirm the definition and use a method designed for that measure rather than rounding this result.
Entering dates as text. A cell can look like a date but contain text, especially when dates are pasted from a website or imported from another system. Excel may then calculate incorrectly or show an error. Check whether changing the cell’s number format affects its display, and test the value in a blank calculation before filling formulas down.
Forgetting the as-of date. TODAY() is convenient for a live tracker, but it recalculates. A report saved today may show different service totals when opened later. Use a fixed date in a clearly labeled cell when the figures need to represent a particular reporting period.
Using the wrong date column or row. A formula copied from another sheet may point to a termination date, birth date, or the wrong employee’s hire date. Click a few result cells and inspect the formula bar to verify that each formula refers to the correct row and field.
Assuming a blank hire date means zero service. A zero can look like a genuine result, even when the date is missing. Use the blank-checking formula if empty rows are expected, and investigate missing dates separately instead of interpreting them as employees with zero years.

Tips

Use an Excel Table for a growing roster. Select the employee data and choose Insert > Table. When you add a new employee row, Excel can extend the calculated column automatically. Keep the hire date and result columns clearly named so the table is easy to review.
Separate the calculation from its display. Keep completed years as a numeric result if you will sort, filter, or group employees by service. Add a separate display column only if you need wording such as “8 years.” Text is less convenient for numerical summaries.
Use a single reporting date for a whole report. A date entered once in E1 makes the calculation easier to audit and keeps every employee on the same snapshot. Make the date visible, and avoid changing it after distributing a report unless you intend to update the results.
Check your organization’s service-date rules. Leave periods, adjusted service dates, acquisitions, or local policies can affect what date should count as a start date. Excel calculates from the date you provide; it cannot determine which employment date a policy requires.
Keep the source date intact. Do not replace the original hire-date column with calculated values. Keeping dates and results in separate columns makes it possible to check or recalculate the service period later.

Troubleshooting

DATEDIF returns #NUM!. Check whether the hire date is later than the end date. This can happen because of a typo, a future start date, or a mistaken reference. Correct the date or use the future-date check shown above to flag it.

The result is #VALUE! or looks wrong. The start date or the reporting date may be stored as text rather than as an Excel date. Select the cell and inspect the formula bar; then re-enter it as a date or convert the imported values to real dates. Regional settings affect how Excel reads dates, so use a format that is unambiguous in your system, such as a date selected through the calendar or an established date format.

The formula appears as text instead of calculating. The result cell may have been formatted as Text before the formula was entered, or the entry may begin with an apostrophe. Set the cell to General, edit the formula, and press Enter again. Also check that Show Formulas has not been turned on; press Ctrl+` on Windows to toggle it. On Mac, use the equivalent Excel command or check Formulas > Show Formulas.

The output shows a date or an unexpected format. Select the result cells and change Home > Number Format to General or Number. DATEDIF returns a number of years; a date format can make that number appear confusing.

The result changes after reopening the workbook. That is expected when the formula uses TODAY(). Use a date stored in a cell as the end date if you need a saved, reproducible report. If a formula does not update after you correct a date, check Formulas > Calculation Options and select Automatic, unless the workbook intentionally uses manual calculation.

Only the first employee has a result. Check that the formula was filled down and that the rows below are not blocked by existing content. Select the first result cell and drag its fill handle down, or select the intended range and use Home > Fill > Down.

Frequently Asked Questions

Does DATEDIF calculate years of service accurately?
For completed whole years between a hire date and an end date, yes. The “y” unit counts full anniversaries, rather than dividing elapsed days by 365 or rounding a decimal.

How do I calculate years of service as of a past date?
Enter the chosen date in a cell such as E1 and use that cell as DATEDIF’s end date. Keep the reference absolute when filling down so every employee is measured against the same reporting date.

Can Excel show years and remaining months?
Yes. With the hire date in B2 and today as the end date, use =DATEDIF(B2,TODAY(),"y")&" years, "&DATEDIF(B2,TODAY(),"ym")&" months". For a hire date of June 15, 2018, on November 3, 2026, this displays 8 years, 4 months. This is a text display; use the years-only numeric result for sorting or arithmetic.

Will the formula work in Excel for Mac or Excel for the web?
Yes. DATEDIF, IF, and TODAY work in current Excel for Mac and Excel for the web, and the formula syntax is the same. Menu labels and keyboard shortcuts can differ slightly on Mac; the formula itself does not.

Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is supported in Excel, but it may not appear in autocomplete or the Insert Function list. You can type it directly into the formula bar or a cell. Check that the spelling and quotation marks match the examples.

How should I handle employees with multiple hire dates?
Use the start date that your reporting rules define as the service date. If an employee has breaks in service or a rehire date, a single date formula will not automatically account for those periods. Confirm the applicable rule and calculate from the correct service date or use a separate calculation for each qualifying period.

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.