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 use the same formula syntax for this pattern.
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 |
Formula explanation
- A2 is the birth date or start date.
- TODAY returns the current date.
- Y returns completed years.
What this formula does
Calculate Age Formula is built around DATEDIF. The example formula on this page is =DATEDIF(A2, TODAY(), "Y"), and it is written against sample columns such as Start Date, End Date, Holiday, Result.
Excel and Google Sheets use the same copy pattern here, so the main work is adjusting ranges, criteria, and output cells to match your sheet.
Date formulas work because spreadsheet dates are stored as serial values. The visible date format can change, but the underlying value must still be a real date for the formula to be reliable.
Use this pattern for this task: Calculate age from date of birth in Excel or Google Sheets. If the result looks like a number, format the output cell as a date or number according to the result you expect.
| Syntax piece | Role in the formula |
|---|---|
| A2 | A2 is the birth date or start date. |
| DATE | TODAY returns the current date. |
| Part 3 | Y returns completed years. |
Practical use cases
=DATEDIF(A2, TODAY(), "Y")Excel: Use this in a timeline, invoice, or task tracker where real date cells control the result. Returns: If A2 is 1990-05-20 and today is 2026-05-20, the result is 36.
=DATEDIF(A2, TODAY(), "Y")Google Sheets: Use this when a report needs a repeatable date calculation that can be filled down row by row. Returns: If A2 is 1990-05-20 and today is 2026-05-20, the result is 36.
Common errors
- Cells that only look like dates may actually be text.
- Inclusive date counts require adding 1 when both start and end dates should count.
- Date formulas can return decimals when source cells contain times.
Common errors and troubleshooting
| Issue | Likely cause | Fix |
|---|---|---|
| Copied formula gives an unexpected result | Cells that only look like dates may actually be text. | Format source cells as dates, remove text dates, and decide whether endpoints should be inclusive or exclusive. |
| Common setup problem | Inclusive date counts require adding 1 when both start and end dates should count. | Format source cells as dates, remove text dates, and decide whether endpoints should be inclusive or exclusive. |
| Common setup problem | Date formulas can return decimals when source cells contain times. | Format source cells as dates, remove text dates, and decide whether endpoints should be inclusive or exclusive. |
| Result shows a serial number | The formula returned a valid date value, but the output cell is formatted as a number. | Change the output format to Date, or keep the number format when the expected answer is an elapsed day count. |
| Date calculation is off by one day | The business rule may count both endpoints, or the source cells may contain time values. | Add 1 only for inclusive elapsed days, and remove times or use explicit boundaries when comparing dates. |
When not to use this formula
- Do not use simple date subtraction for business days; use NETWORKDAYS or WORKDAY.
- Do not use DATEDIF for rounded month math; it returns completed periods.
Alternatives
| Alternative | When to use it |
|---|---|
| Date Difference Formula Builder | Use for days, completed months, or completed years. |
| Excel Date Serial Converter | Use when imported dates arrive as serial numbers. |
| FILTER Rows by Date | Use when the goal is returning rows by date instead of calculating one value. |
Compare with related formulas
| Formula or tool | How it differs | Use it instead when |
|---|---|---|
| Date Difference Formula Builder | Date Difference Formula Builder solves a nearby workflow, while Calculate Age Formula is focused on calculate age from date of birth in excel or google sheets. | Use for days, completed months, or completed years. |
| Excel Date Serial Converter | Excel Date Serial Converter solves a nearby workflow, while Calculate Age Formula is focused on calculate age from date of birth in excel or google sheets. | Use when imported dates arrive as serial numbers. |
| FILTER Rows by Date | FILTER Rows by Date solves a nearby workflow, while Calculate Age Formula is focused on calculate age from date of birth in excel or google sheets. | Use when the goal is returning rows by date instead of calculating one value. |
Test cases
| Test | Platform | Formula | Returns |
|---|---|---|---|
| Excel copy output | Excel | =DATEDIF(A2, TODAY(), "Y") | If A2 is 1990-05-20 and today is 2026-05-20, the result is 36. |
| Google Sheets copy output | Google Sheets | =DATEDIF(A2, TODAY(), "Y") | If A2 is 1990-05-20 and today is 2026-05-20, the result is 36. |
Related formulas
FAQ
What does the Calculate Age Formula do?
It supports this task: Calculate age from date of birth in Excel or Google Sheets. The example uses A2, but you should replace the ranges and criteria with the cells in your own workbook.
Does the Calculate Age Formula work in Excel and Google Sheets?
Yes. The Excel and Google Sheets versions on this page use the same syntax for this pattern.
What should I change before copying the formula?
Change the source ranges, criteria cells, and output range references so they match your sheet. Keep related ranges on the same rows unless the formula notes say otherwise.
Why does my date formula show a number?
The result is probably a valid spreadsheet date serial. Format the output cell as Date when the answer should display as a date.
Does this count both the start and end date?
It depends on the formula. Functions like NETWORKDAYS count both the start and end dates by default, while a plain end-minus-start subtraction does not. Add 1 only when your rule should count both endpoints, and check the expected result section for this formula.
What if my date is stored as text?
Convert text dates to real spreadsheet dates before relying on date math. Text that only looks like a date can produce wrong results.