Completed years of service
An Excel formula exercise from Formuladrill. Solve it with a formula bar on a live sheet in your browser, and it is checked the moment you submit.
The task
Work out each employee's completed years of service on the date in F1. A year only counts once it is complete: someone hired on 2019-07-01 has 6 full years on 2026-06-30. Fill C2 and C3.
What this exercise teaches
DATEDIF counts complete units: "Y" for years, "M" for months, "D" for days. It is the reliable way to get an age or a length of service, because dividing the days by 365 goes wrong around leap years and anniversaries.
A hint if you get stuck
DATEDIF(start, end, "Y") returns the number of complete years between two dates. Excel does not suggest this function as you type, but it works.
Try it now, free, no account needed
Your first 15 exercises on Formuladrill are free. Open it on the practice page, solve it, and get instant feedback with a hint. A free account adds the worked solution; Pro unlocks every exercise, timed mode and the certificate.
Open this exerciseMore exercises in Text and dates
- Build an email address
- Pull out the domain
- Payment due dates
- Clean up names
- Join the tags
- Digits only
- Days between dates
- Year and month
- Contract end date
- Pay by month end
- Split last name, first name
- Calendar quarter
- Month labels with TEXT
- IDs with leading zeros
- Mask an account number
- File extension
- Working days on a task
- Ship-by dates
- Weekend or weekday
- Days in the month