To calculate years of service in Excel, subtract an employee’s start date from a reference date and choose whether to count completed years or a decimal duration.
The right formula depends on how your organization treats anniversaries, future dates, and employees hired on February 29.
Choose what “years of service” means
Most service reports count only full years completed by a specific date.
For example, someone hired on July 15, 2020, has completed five years on July 15, 2025, but has not completed six years until July 15, 2026.
This anniversary-based result is different from dividing the number of days employed by 365.
Before writing a formula, decide whether you need completed years as of today, completed years as of a report date, or an approximate number of years that includes a fraction.
These are different measures, so label the result accordingly.
Calculate completed years of service with DATEDIF
If the employee’s start date is in cell A2, use this formula to return completed years through today:
=DATEDIF(A2,TODAY(),"Y")DATEDIF returns the number of whole years between two dates.
For example, if A2 contains 7/15/2020 and today is 7/14/2026, the result is 5.
On July 15, 2026, the result becomes 6.
For a fixed reporting date in B1, replace TODAY() with that cell:
=DATEDIF(A2,$B$1,"Y")The dollar signs keep the reference to B1 fixed when you fill the formula down a column.
This approach makes historical reports reproducible: the service calculation uses the date stored in the report rather than the date someone opens the workbook.
Calculate years, months, and days of service
To show a tenure breakdown such as “5 years, 3 months, 2 days,” combine three DATEDIF calculations.
If the start date is in A2 and the as-of date is in B1, use:
=DATEDIF(A2,$B$1,"Y")&" years, "&DATEDIF(A2,$B$1,"YM")&" months, "&DATEDIF(A2,$B$1,"MD")&" days"The Y unit counts complete years, YM returns the remaining months after complete years, and MD returns the remaining days after those months.
If A2 is January 10, 2020, and B1 is April 12, 2025, the displayed breakdown is 5 years, 3 months, 2 days.
DATEDIF is supported in Excel and Google Sheets, although Excel may not show it in the formula autocomplete.
Use a valid start date that is earlier than or equal to the end date, especially when relying on the month-and-day units.
Use an alternative formula for completed years
If you prefer not to use DATEDIF, calculate calendar-year difference and subtract one when the anniversary has not yet arrived:
=YEAR(B1)-YEAR(A2)-IF(DATE(YEAR(B1),MONTH(A2),DAY(A2))>B1,1,0)Here, A2 is the start date and B1 is the as-of date.
The formula first subtracts the years, then checks whether the anniversary date in the reporting year is later than the as-of date.
If it is, the formula subtracts one.
This formula is available in current desktop versions of Excel and Google Sheets.
It can also handle a February 29 start date through the spreadsheet’s date rules, but organizations should decide how to define the anniversary in non-leap years.
Test that policy with a known example before using the result for benefits or eligibility decisions.
Calculate an approximate decimal number of years
For an approximate tenure value that includes a fraction, divide elapsed days by 365.25:
=(B1-A2)/365.25If the employee worked 1,826 days, this returns about 5.00 years.
Format the result as a number with one or two decimal places rather than as a date.
The divisor of 365.25 accounts approximately for leap years, but it does not enforce anniversary-based service rules.
Use it for rough duration comparisons, not for a policy that awards benefits after a specific completed anniversary.
You can use YEARFRAC when a day-count basis matters:
=YEARFRAC(A2,B1,1)The third argument, 1, selects the Actual/Actual basis.
Other basis values use different day-count conventions, which can produce different fractions.
Excel and Google Sheets both support YEARFRAC; choose the basis that matches the reporting or finance rule rather than treating the result as interchangeable with completed service years.
Handle blank, future, and invalid dates
Blank cells and future start dates can produce confusing results or errors.
Add checks so the formula returns a useful message instead of calculating an unintended tenure.
For completed years as of B1, use:
=IF(A2="","",IF(A2>$B$1,"Start date is after report date",DATEDIF(A2,$B$1,"Y")))This leaves the result blank when there is no start date and flags a start date later than the reporting date.
If you prefer a numeric result for future dates, choose an explicit policy, such as returning zero, and adjust the formula accordingly.
Confirm that the cells contain real spreadsheet dates; text that merely looks like a date may not behave as expected.
Format and fill the service formula
Enter each employee’s start date in column A, put the report date in B1, and enter the formula in C2.
Fill it down for the remaining employees.
Format the start-date and report-date cells as dates, and format completed-year results as numbers with zero decimal places.
For a live service report, use TODAY() so the result updates as the current date changes.
For a payroll, audit, or annual review file, a fixed date in a cell is often easier to review later.
To preserve a report snapshot, record the chosen as-of date alongside the results.
Common formula problems
- The result is one year lower than expected: Check whether the employee’s anniversary has passed by the as-of date.
Completed-years formulas intentionally do not round up. - The formula returns an error: Check that both date cells contain valid dates and that the start date is not after the end date.
- The result looks like a date: Change the result cell’s number format to General or Number.
- Copied formulas use the wrong report date: Use an absolute reference such as
$B$1for the as-of date. - Leap-day service differs from policy expectations: Define whether the anniversary is treated as February 28 or March 1 in non-leap years, then verify the spreadsheet’s result against that rule.
For most employee lists, =DATEDIF(A2,$B$1,"Y") is the simplest way to count full years of service on a chosen date.
Use a decimal-year formula only when an approximate duration is the intended measure, and keep the reporting date and anniversary policy visible in the workbook.