DATEDIF vs YEARFRAC
DATEDIF gives whole years, months or days; YEARFRAC gives years with decimals.
| DATEDIF | YEARFRAC | |
|---|---|---|
| Result | Whole: 3 years | With decimals: 3.42 years |
| Shows in the function wizard | No (hidden function) | Yes |
| Leftover months or days | Yes, with "YM" and "MD" | No |
| If the start date is later | #NUM! | Swaps them |
Syntax and example
DATEDIF
=DATEDIF(start_date, end_date, unit)
=DATEDIF(B2,$F$1,"Y")
Calculates full years, months or days between two dates. It is how you calculate age or tenure.
YEARFRAC
=YEARFRAC(start_date, end_date, [basis])
=YEARFRAC(B2,$I$1)
Returns the years between two dates with decimals: 2.5 is two and a half years.
Which one to use?
For age or seniority in completed years, DATEDIF with "Y". For prorating (vacation, interest), YEARFRAC.
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
Seniority, text dates and ISO weeks
YEARFRAC(start, end) gives the years between two dates with decimals: 2.5 is two and a half years. DATEVALUE turns a date stored as text (like "2024-03-15", typical of a CSV) into a real date. ISOWEEKNUM gives the ISO week, used in logistics and payroll.
In C2, calculate each employee’s years of service as of the cutoff date in I1.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Hired | Years | Last payment (text) | Last payment | ISO week | Cutoff | 12/31/2025 | ||
| 2 | Ana | 03/15/2019 | 2025-11-28 | |||||||
| 3 | Beto | 07/01/2022 | 2025-12-05 | |||||||
| 4 | Carla | 01/20/2015 | 2025-12-12 | |||||||
| 5 | Dani | 10/07/2024 | 2025-12-19 | |||||||
| 6 | Elena | 12/31/2020 | 2025-12-26 | |||||||
| 7 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What is the difference between DATEDIF and YEARFRAC?
- DATEDIF gives whole years, months or days; YEARFRAC gives years with decimals.
- When should I use DATEDIF or YEARFRAC?
- For age or seniority in completed years, DATEDIF with "Y". For prorating (vacation, interest), YEARFRAC.