Date formula page
Calculate Age Formula
Use DATEDIF when age should count completed years rather than rounded year differences.
Calculate age from date of birth in Excel or Google Sheets.
If A2 is 1990-05-20 and today is 2026-05-20, the result is 36.
Copy formulas
=DATEDIF(A2, TODAY(), "Y")=DATEDIF(A2, TODAY(), "Y")Excel and Google Sheets both support DATEDIF(A2, TODAY(), "Y") for completed years. TODAY makes the displayed age update when the current date changes.
Example data
| Start Date | End Date | Holiday | Result |
|---|---|---|---|
| 2026-01-01 | 2026-01-31 | 2026-01-19 | 30 |
| 2026-02-10 | 2026-03-12 | 30 | |
| 1990-05-20 | 2026-05-20 | 36 |
If A2 is 1990-05-20 and today is 2026-05-20, the result is 36.
How the formula works
- A2 is the birth date or start date.
- TODAY returns the current date.
- Y returns completed years.
| Syntax piece | Role in the formula |
|---|---|
| A2 | The birth date or other starting date. |
| TODAY() | The current date used as the end date. |
| "Y" | The DATEDIF unit for completed whole years. |
Verified examples
=DATEDIF(A2, TODAY(), "Y")Excel: Set A2 to =EDATE(TODAY(),-12*36). Returns: 36
=DATEDIF(DATE(1990,5,20),DATE(2026,5,20),"Y")Google Sheets: Use the fixed dates in the formula. Returns: 36
=DATEDIF(DATE(1990,5,20),DATE(2026,5,19),"Y")Excel: Use the fixed dates in the formula. Returns: 35
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| DATEDIF returns #NUM! | The birth date is later than TODAY or the end date. | Validate that the starting date is not in the future before calculating age. |
| A valid date is treated as text | The imported date is text rather than a spreadsheet date serial. | Convert the source to a real date with DATEVALUE or a locale-appropriate date conversion. |
| The result changes around a birthday | The Y unit reports completed years, so it changes on the birthday and relevant leap-day boundary. | Use a different date-difference definition if anniversary behavior is not the intended rule. |
When not to use this formula
- Do not use completed-year age when the calculation must include months, days, or a legal age rule defined outside the workbook.
Alternatives
| Alternative | When to use it |
|---|---|
| Days Between Dates Formula | Use when elapsed days rather than completed years are required. |
| Date Difference Formula Builder | Use when the output unit and date boundaries need guided selection. |
Related formulas
Official references
- DATEDIF function from Microsoft
FAQ
Does DATEDIF with Y return completed years?
Yes. The "Y" unit returns whole years completed between the two dates.
Why does TODAY make the age update?
TODAY recalculates to the current date, so the completed-year result changes when the next birthday boundary is reached.