You have an employee list with hire dates and HR asks for seniority in years and months. You subtract dates, divide by 365, and somebody’s figure comes out a year too high or too low.
How to use DATEDIF Formula step by step
1. Choose the result
A single unit (years, months or days) or the full text "X years, Y months and Z days".
2. Give the dates
The start date is a cell; the end can be another cell or TODAY(), which refreshes daily.
3. Pick the unit and copy
Y for whole years, M for whole months, D for days, and YM, MD or YD for the remainders after years or months.
The Step Count: Traditional Friction vs. KorexTools
Subtracting dates and dividing by 365 (4 steps)
- Subtract the hire date from today.
- Divide by 365 to get years.
- Notice leap years throw the figure off.
- Patch by hand the cases where it fails.
With the KorexTools “DATEDIF Formula” builder (3 steps)
- Pick years, months or full text.
- Give the dates.
- Paste the formula in the first row and drag it.
DATEDIF has existed for decades, works fine, and Excel still leaves it out of the wizard
When you type =DATEDIF( in Excel the tooltip with the arguments does not appear, which is why many people think it does not exist. It comes from Lotus 1-2-3 and stays for compatibility. Its advantage over subtracting dates is that it returns whole years or months, not days you then divide by 365, a calculation that breaks on leap years.
The builder composes the formula with the unit you ask for and, for the full age, chains three DATEDIFs with the units Y, YM and MD plus the words for years, months and days in the function language you chose. With the end date set to TODAY() the result refreshes each time the workbook opens.
Which DATEDIF unit returns what
- •"Y": whole years elapsed, ideal for age or seniority.
- •"M": whole months, handy for contracts or payment terms.
- •"YM", "MD" and "YD": what is left after removing years or months, for building text such as "3 years, 2 months".
DATEDIF units with 15/03/2020 and 20/05/2024
| Unit | What it returns | Result |
|---|---|---|
| "Y" | Whole years | 4 |
| "M" | Whole months | 50 |
| "YM" | Months after the years | 2 |
Common Mistakes and How to Avoid Them
- ⚠️Typing dates as text "15/03/2020" instead of using cells formatted as dates.
- ⚠️Mixing day/month and month/day order when importing data.
Frequently Asked Questions
Why does Excel not suggest DATEDIF as I type it?
It is a legacy function that Microsoft never added to the wizard. It works the same if you type it in full, with all three arguments.
Why do I get #NUM!?
The end date is earlier than the start date. Swap them, or check that both cells hold real dates and not text.
How do I get an age in exact years as of today?
Choose "Today" as the end date and the "Whole years" unit. The result changes every day without touching the formula.
What is SIFECHA called in English?
It is DATEDIF. The tool writes the name that matches your language, but the file always stores the English version, so a sheet built with SIFECHA opens fine in an English Excel.
How do I work out the time between two dates using today’s date?
Pick “Today (updates itself)” as the end date. The formula uses TODAY, so the age or tenure grows every day without you touching anything; for a fixed date use “A cell”.
What I would do if I were in your shoes
«Before computing the age of a thousand people, test the formula with a birth date that has its birthday today: it is the edge case where homemade calculations go wrong most often.» — DataKorex Engineering Team.