Google Sheets DATEDIF function
Calculates full years, months or days between two dates. Useful for ages and tenure. In Spanish Sheets it is called SIFECHA.
Syntax
English Sheets
=DATEDIF(start_date, end_date, unit)
Spanish Sheets (Mexico locale)
=SIFECHA(fecha_inicio, fecha_fin, unidad)
Spanish Sheets (Spain, Latin America)
=SIFECHA(fecha_inicio; fecha_fin; unidad)
| Argument | What it is |
|---|---|
| start_date | The earlier date. |
| end_date | The later date. |
| unit | "Y" years, "M" months, "D" days, "YM" leftover months… |
Example
DATEDIF(start, end, "Y") gives the full years between two dates; "M" gives months and "D" days. For today’s date you would use TODAY(), which recalculates every day; here we use a fixed cutoff date.
=DATEDIF(B2,$F$1,"Y")
Common mistake
If the start date is after the end date it returns #NUM!. For age today use TODAY() as the end date.
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 DATEDIF
Type the formula in the sheet and check it instantly. Spanish or English function names both work. See all lessons
Age and tenure with DATEDIF
DATEDIF(start, end, "Y") gives the full years between two dates; "M" gives months and "D" days. For today’s date you would use TODAY(), which recalculates every day; here we use a fixed cutoff date.
In C2, calculate each employee’s full years of service as of the cutoff date in F1.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Start date | Years | Cutoff date | 12/31/2026 | ||
| 2 | Marisol | 03/16/2015 | |||||
| 3 | Fernando | 12/31/2021 | |||||
| 4 | Gabriela | 08/01/2019 | |||||
| 5 | Hugo | 02/10/2026 | |||||
| 6 | Daniela | 01/07/2008 | |||||
| 7 |
Tip: while typing, click a cell or drag across several to insert their reference.
Frequently asked questions
- What is DATEDIF called in Spanish?
- In Spanish Google Sheets it is SIFECHA. The formula =DATEDIF(B2,$F$1,"Y") is written =SIFECHA(B2;$F$1;"Y") (or with commas under a Mexico locale).
- Why does DATEDIF use semicolons in some spreadsheets?
- It depends on each spreadsheet's locale, under File > Settings. Where the comma is the decimal mark (Spain, much of South America), arguments are separated by semicolons. Mexico and United States locales use commas.
- What is the most common DATEDIF mistake?
- If the start date is after the end date it returns #NUM!. For age today use TODAY() as the end date.