# How to calculate years of service in Excel

Use `=DATEDIF(hire_date, as_of, "y")&" years, "&DATEDIF(hire_date, as_of, "ym")&" months"` for completed years and months.

**Excel:** `=DATEDIF(B2,$F$1,"y")&" years, "&DATEDIF(B2,$F$1,"ym")&" months"` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Employee | Hire date | Service |  | As of | 2026-12-31 |
| R. Alvarez | 2011-03-14 |  |  |  |  |
| K. Osei | 2019-08-01 |  |  |  |  |
| M. Chen | 2024-01-15 |  |  |  |  |
| T. Brooks | 2026-02-02 |  |  |  |  |
| S. Novak | 2001-12-31 |  |  |  |  |

Formula in C2, filled to C6: `=DATEDIF(B2,$F$1,"y")&" years, "&DATEDIF(B2,$F$1,"ym")&" months"`

Result: 15 years, 9 months; 7 years, 4 months; 2 years, 11 months; 0 years, 10 months; 25 years, 0 months

## How it works

DATEDIF counts complete units: `"y"` gives the whole years since the hire date and `"ym"` the whole months after the last work anniversary. S. Novak was hired on December 31, 2001, so on December 31, 2026 the service is exactly 25 years.

## Other ways

- `=DATEDIF(B2,$F$1,"y")`: Whole years only, for service awards or PTO tiers. (Completed years as a number.)
- `=ROUNDDOWN(($F$1-B2)/365.25,1)`: Decimal years like 15.8, close enough for a report. YEARFRAC is the exact function for this. (Approximate decimal years.)

## Common mistakes

- `YEAR(F1)-YEAR(B2)` counts calendar years, not service: someone hired on December 31 would show a full year on January 1.
- Keep the as-of date in a cell. With TODAY() inside the formula, last year's report changes every time you open it.
- A hire date stored as text returns #VALUE!. Convert it first.

## Questions

**How do I calculate service in years with decimals?**

YEARFRAC returns fractional years: `=YEARFRAC(B2,$F$1)` gives about 15.8 for R. Alvarez.

**How do I find who reaches 10 years this year?**

Check the year of the 10-year anniversary: `=YEAR(EDATE(B2,120))=YEAR($F$1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/years-of-service