To calculate completed years of service from a hire date in Excel, use =DATEDIF(A2,TODAY(),"Y"), assuming the hire date is in A2. The result is the number of full anniversaries reached as of today; for a fixed reporting date, replace TODAY() with a cell containing that date.
The basic DATEDIF formula
DATEDIF compares a start date with an end date and returns the difference in the unit you request. Its third argument is a text code, and "Y" means completed years. This makes it a direct fit for a common HR question: how many whole years has someone worked as of a particular date?
With the hire date in A2 as June 15, 2018, and with the formula evaluated on November 2, 2026, =DATEDIF(A2,TODAY(),"Y") returns 8. The employee has passed eight hire-date anniversaries but has not yet reached the ninth. The answer is not 2026 minus 2018, which would incorrectly return 8 in some parts of the year and 9 in others.
Enter the formula in another cell on the same row, such as B2, and fill it down for additional employees. In Microsoft 365 for Windows, you can drag the fill handle at the lower-right corner of the cell, or select the formula cell and use Ctrl+C, select the destination cells, and press Ctrl+V. On Mac, use Command+C and Command+V instead. Excel for the web also supports the formula and the fill handle.
Use a fixed date for reports
TODAY() updates when Excel recalculates the workbook, so a service-years report based on it can show different results on a later day. That is useful for a live roster, but it is not ideal when you need a report whose results remain tied to a specific cutoff date. Put the cutoff date in a cell and refer to that cell instead.
With hire dates in A2:A20 and a single reporting date in B1—for example, November 2, 2026—enter this in C2 and copy it down: =DATEDIF(A2,$B$1,"Y"). For a hire date of June 15, 2018, the result is 8. The dollar signs keep the reporting-date reference fixed as the formula is copied, while A2 changes to A3, A4, and so on.
For a one-time, static report, enter the cutoff date as a real Excel date rather than relying on a volatile current date. You can type a date into B1 and format it using Home > Number. The exact date display depends on your regional settings, so a value displayed as 11/2/2026 in one workbook may use a different order in another. What matters is that Excel recognizes it as a date, not text.
Calculate years, months, or both
Choose the result that matches the question being asked. Completed years are usually appropriate for statements such as “eight years of service.” A benefits calculation or an internal report may instead need completed months, or a more descriptive years-and-months breakdown.
- Completed years: Use the
"Y"unit for whole anniversaries reached. - Completed months: Use
"M"to count whole months between dates. - Remaining months after whole years: Use
"YM"to return the month portion after full years are removed.
With a hire date of June 15, 2018, in A2 and an as-of date of November 2, 2026, in B2, =DATEDIF(A2,B2,"M") returns 100 completed months. With the same cell layout, =DATEDIF(A2,B2,"YM") returns 4 remaining months after the eight completed years. These are separate calculations, so label the output columns clearly; a value of 4 by itself could otherwise be mistaken for four years.
You can display the components in separate columns, which is often easier to sort, filter, or use in later calculations. Avoid treating a years-and-months label as a numeric decimal: “8 years and 4 months” is not the same as 8.4 years. Four months is one-third of a year, not four-tenths.
Show fractional years when appropriate
Some analyses need a fractional year rather than the number of completed anniversaries—for example, a chart that treats service duration as a continuous value. Excel’s YEARFRAC function returns a fraction of a year between two dates. Its third argument selects a day-count basis, so check that the chosen convention matches the purpose of the calculation.
With a hire date of January 1, 2024, in A2 and an as-of date of January 1, 2025, in B2, =YEARFRAC(A2,B2,1) returns 1. The third argument, 1, specifies the Actual/Actual basis. With other dates, the result may be a decimal, and different basis choices can produce different fractions. Do not substitute this result for completed service years when a policy explicitly counts anniversaries.
Rounding also changes the meaning. Rounding 8.9 to a whole number gives 9 even if the employee has not reached the ninth anniversary. If the intended measure is completed years, use DATEDIF with "Y" rather than rounding a fractional-year result.
Prepare hire dates and handle blank rows
Excel date formulas require dates that Excel recognizes as date values. Dates imported from a payroll system or pasted from a text file may look like dates but be stored as text. If a formula returns an error or behaves unexpectedly, check a sample cell’s value and formatting. Changing a cell’s number format alone does not convert text into a real date.
For a list with empty rows, you can keep the results column tidy by returning a blank when the hire-date cell is empty. With hire dates in A2:A20, put this in B2 and copy it down: =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). For a valid hire date of June 15, 2018, evaluated on November 2, 2026, it returns 8; if A2 is blank, it returns a blank cell. The formula does not validate text dates or future dates, so those still need separate attention.
A future hire date is a useful data-quality check. If the start date is later than the end date, DATEDIF returns an error rather than a negative number of years. Confirm whether the date was entered incorrectly, the employee has a future start date, or the chosen reporting date is earlier than the hire date. Do not silently replace the error with zero unless zero is genuinely the intended business meaning.
Choose the right service-year definition
“Years of service” can mean different things in different settings. The formula can calculate date intervals, but it cannot decide which company rule applies. A report may count full anniversaries, elapsed calendar years, partial years, or service only from a specified effective date. Establish the definition before selecting a formula, especially if the result affects benefits, leave, or eligibility.
- Whole years completed: Use
DATEDIFwith"Y"when each anniversary advances the count. - Whole months completed: Use
DATEDIFwith"M"if the reporting rule is based on completed months. - Elapsed fraction of a year: Use
YEARFRAConly after selecting the day-count basis that fits the analysis. - Service adjusted for breaks or prior service: A single hire-date formula may not be enough. You may need a policy-approved adjusted date or separate records for periods of employment.
For example, subtracting the hire year from the current year is not equivalent to completed years. Someone hired on December 20, 2020, has not completed six years on December 19, 2026, even though the calendar-year subtraction is 6. The anniversary-based answer is still 5 on that date. This distinction is a frequent source of off-by-one errors in manually built reports.
Common errors and how to fix them
If Excel displays #NUM!, check that the start date is not after the end date. If it displays #VALUE!, one of the inputs may be text rather than a valid date. If the result is a number that seems one year too high, confirm that the formula uses "Y" and that the dates are in the expected cells. Also verify that the report’s as-of date is correct.
DATEDIF is a supported Excel function, but it may not appear in the formula autocomplete list while you type. You can still enter it directly. Its unit argument must be quoted, and the start date must come before or be the same as the end date. For an ordinary hire-date calculation, the completed-years unit is the relevant one; other unit options are not interchangeable.
Another common mistake is interpreting a cell’s display as its underlying value. A cell formatted as a date may display a serial number, or a number-format choice may make a date look unfamiliar. Select the cell and inspect the formula bar, then confirm that the displayed result is a number of years and that the output column has a suitable General or Number format. Do not apply a date format to the years result.
A practical setup for an employee list
A simple worksheet can make the calculation easier to review and maintain. Use one row per employee, with a clearly labeled hire-date column and a separate output column. If the report needs a consistent cutoff, store that date in one labeled cell rather than entering it independently in every row.
- Enter a heading such as Hire Date in A1 and Years of Service in B1.
- Enter each employee’s hire date in A2:A20 and confirm the entries are valid Excel dates.
- For a live calculation, enter
=DATEDIF(A2,TODAY(),"Y")in B2. For a fixed cutoff in C1, use=DATEDIF(A2,$C$1,"Y")instead. - Copy the formula down the output column and check a few results against the relevant hire-date anniversaries.
- Label the report’s as-of date so readers know when the figures apply.
In Excel for Microsoft 365 on Windows, these formulas work without enabling an add-in. The same worksheet formulas work in Excel for Mac and Excel for the web; differences in date display or regional separators may depend on settings. The formulas shown use commas, as required by the usual English-language syntax; some regional Excel configurations display semicolons as argument separators.
Frequently Asked Questions
How do I calculate years of service from a hire date in Excel?
With the hire date in A2, enter =DATEDIF(A2,TODAY(),"Y") to return completed years as of today. Replace TODAY() with a cell reference if you need a fixed as-of date.
Does DATEDIF round years of service?
No. The "Y" unit returns completed whole years, so a person who is close to an anniversary remains at the lower number until that anniversary is reached.
How can I calculate years and months of service?
Calculate completed years with DATEDIF and "Y", and remaining months with DATEDIF and "YM". Keep the values in clearly labeled columns so the month remainder is not confused with total months.
Why does DATEDIF return an error?
Check that both inputs are valid dates and that the hire date is not later than the as-of date. Blank cells, dates stored as text, and future hire dates are common causes of unexpected results.
Should I use YEARFRAC or DATEDIF?
Use DATEDIF with "Y" for completed anniversary years. Use YEARFRAC when you specifically need a fractional year and have chosen an appropriate day-count basis.
Related
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Rank Formula for Scores: How to Rank ResultsTo rank scores in Microsoft Excel for Microsoft 365, use =RANK.EQ(B2,$B$2:$B$6,0) when the score to rank is in…
- VLOOKUP in Excel for Multiple Columns: Methods and TipsYes. In Excel for Microsoft 365, VLOOKUP can return values from multiple columns either by copying one formula…
- Excel Formula for Current Time: NOW, TIME, and UpdatesTo show the current date and time in Excel, enter =NOW(). To show only the time, use the…
- Excel Text Expansion: AutoCorrect, Formulas, and MoreExcel has no single feature called “text expansion,” but you can make a short abbreviation expand as you…
- Excel MIN Without Blanks: What the Formula IgnoresIf your values are in A2:A10, use =MIN(A2:A10) to find the smallest number while ignoring blank cells. Excel’s…
- How to Filter Rows by Color in Excel: A Practical GuideTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.