Excel Tutorials · Lesson 17
Subtracting two dates gives you a number of days, which is almost never the answer anyone wanted. This lesson covers the functions that turn a birth date or a hire date into a real age or a real length of service: DATEDIF for whole completed years, YEARFRAC for decimals, and TODAY so the number updates itself every morning without you touching the file.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
Excel stores every date as a serial number counting days from January 1, 1900. That is why subtracting a hire date from today's date returns something like 3,847 instead of a length of service, the result is technically correct and practically useless. Dividing that number by 365 gets you close, but leap years pull the answer off by a fraction, and "10.5 years" is rarely what a manager wants to read on a roster anyway.
The function built for this is DATEDIF, which takes a start date, an end date, and a unit code telling it what to count. =DATEDIF(A2,TODAY(),"Y") returns completed whole years, handling leap years and partial years correctly on its own. Age and tenure are the same calculation with different starting points, one counts from a birth date and one counts from a hire date, so every technique on this page applies equally to both.
DATEDIF has an unusual history. It is a compatibility function carried over from Lotus 1-2-3, and Excel does not offer it in the formula autocomplete list or show argument tooltips while you type it. It works perfectly in every version, including Microsoft 365 and Google Sheets, but you have to know the spelling and the unit codes yourself, which is the main reason most people have never encountered it.
The other half of the job is deciding whether the number should be static or live. Typing a fixed as-of date gives you a snapshot that never changes, which is what you want for a report tied to a specific quarter. Using TODAY() makes the number recalculate every time the file opens, which is what you want for an active headcount or roster.
Step-by-step, matching the video above.
Click a date cell and check the right side of the status bar or press Ctrl+1. If the value is left-aligned by default it is text, not a date, and no date function will work on it until it is converted.
For age, that is the birth date. For tenure, the hire date. Both work identically from here, the only difference is what the number means when you are done.
Use TODAY() for a number that updates itself every day the file is opened, or point to a cell holding a fixed as-of date when the figure needs to stay frozen for a report.
In the result cell, type =DATEDIF(A2,TODAY(),"Y"). Excel will not autocomplete DATEDIF or show tooltips as you type, that is expected behavior, keep typing and it will calculate.
If the answer shows as a date rather than a count, the cell inherited date formatting. Select it, press Ctrl+1, and choose General or Number to see the actual figure.
Combine two DATEDIFs with text: =DATEDIF(A2,TODAY(),"Y")&" yrs "&DATEDIF(A2,TODAY(),"YM")&" mos" returns something like 7 yrs 4 mos in one cell.
Double-click the fill handle to copy down, then check the oldest and newest rows. Any negative result means a start date sitting after the end date, which is almost always a typo in the year.
| A: Hire Date | B: Whole Years | C: Years & Months | D: Decimal Years |
|---|---|---|---|
| 2015-03-14 | 11 | 11 yrs 5 mos | 11.47 |
| 2019-11-01 | 6 | 6 yrs 10 mos | 6.84 |
| 2024-06-30 | 2 | 2 yrs 2 mos | 2.18 |
| 2026-08-15 | 0 | 0 yrs 0 mos | 0.05 |
Column B uses =DATEDIF(A2,TODAY(),"Y"), column C adds a second DATEDIF with the "YM" unit joined by text, and column D uses =ROUND(YEARFRAC(A2,TODAY()),2). Notice the newest hire shows 0 whole years but 0.05 decimal years, which is exactly why the right choice depends on what the number is being used for.
Six codes, and three of them are the ones almost nobody knows about.
Returns the number of full years between the two dates, ignoring any leftover months and days. This is the standard answer for both age and tenure, and the one you will use most.
Total whole months across the entire span. A seven-year tenure returns 84 or more here, not the leftover months, which surprises people expecting a number under 12.
Total days between the dates, the same result as simple subtraction. Useful for short spans like days since last contact rather than years of service.
The months remaining once whole years are removed, always a number from 0 to 11. This is the one you pair with "Y" to produce a readable "7 years 4 months" result.
Days remaining after whole months are removed. Microsoft advises caution with this code because it can return a negative result across certain month boundaries, so avoid relying on it for anything official.
Days remaining once whole years are stripped out, which is how you calculate how many days until someone's next birthday or work anniversary.
When you need to average, chart or compare service lengths rather than display them.
=YEARFRAC(A2,TODAY()) returns a decimal like 11.4712. Wrap it in ROUND to control the display: =ROUND(YEARFRAC(A2,TODAY()),1) gives 11.5.
You cannot average whole-year DATEDIF results honestly, because everyone from 5 years and 1 day to 5 years and 364 days counts identically as 5. Averaging YEARFRAC results gives a true mean tenure.
YEARFRAC accepts an optional fifth setting controlling day-count convention. The default, 0, uses the US 30/360 method. Setting it to 1 uses actual days, which is more accurate for HR reporting and worth specifying.
Rounding a decimal can push someone to their next birthday early. For anything where the number represents a person's stated age, use DATEDIF with "Y", which only counts birthdays that have actually happened.
Choosing between a number that moves and a number that stays put.
TODAY takes no arguments and returns the current date, recalculating whenever the workbook opens or recalculates. An active roster should use it so tenure is never stale.
Put your as-of date in one cell, reference it with an absolute reference like $B$1, and every row calculates against the same fixed point. Change one cell to re-run the whole report on a new date.
NOW returns date and time together. The time portion adds a fraction that can shift a boundary calculation by a day, and it forces recalculation constantly. TODAY is the correct function for date-only math.
To lock a calculated tenure into a static value, copy the column and use Paste Special > Values over itself. The numbers stop updating, which is what an archived snapshot needs.
| A: Birth Date | B: =DATEDIF(A2,TODAY(),"Y") | C: Days to Next Birthday |
|---|---|---|
| 1988-01-22 | 38 | 141 |
| 1995-09-30 | 30 | 27 |
| 2001-12-05 | 24 | 93 |
Column B counts only birthdays that have already occurred, which is the correct behavior for a stated age. Column C uses 365 minus =DATEDIF(A2,TODAY(),"YD") to count forward to the next anniversary, the same technique that drives work anniversary reminders on an employee roster.
Nearly identical, with one meaningful improvement.
Google Sheets supports DATEDIF with the same six unit codes and the same syntax, and unlike Excel it does list the function in autocomplete with documentation attached.
Both functions behave identically in Sheets, so a workbook built with these formulas converts between the two applications without any rewriting.
Sheets recalculates TODAY on open and periodically while the file is open, so a tab left open overnight rolls over on its own. Excel updates on open and on recalculation.
Small touches that make a tenure column readable at a glance.
Join two DATEDIFs with the ampersand operator and literal text between them. Add IF logic if you want the word to switch between "year" and "years" for a value of one.
Apply a rule to the tenure column highlighting anything at or above five years, so long-service anniversaries stand out without anyone reading every row.
Wrap the result in IFS to bucket people into 0-1, 1-3, 3-5 and 5+ year bands, which is far more useful than raw numbers once you feed the column into a PivotTable.
An empty start date produces a wrong or negative result. Wrap the formula in =IF(A2="","",DATEDIF(...)) so unfilled rows stay visually clean.
This ignores leap years and drifts further off the longer the span. A 40-year tenure can land a full year wrong. DATEDIF handles the calendar correctly on its own.
Imported dates often arrive as text that merely looks like a date. Every date function will fail or return an error until you convert the column with Text to Columns or DATEVALUE.
"M" returns total months across the entire span, so a seven-year tenure returns 84. The code for leftover months after whole years is "YM".
Rounding a decimal can age someone up months before their actual birthday. Use DATEDIF with "Y" whenever the number will be shown as an age.
NOW includes a time fraction that can tip a boundary calculation across a day and forces constant recalculation. TODAY is the right function for date-only math.
If the cell inherited date formatting, a tenure of 11 will display as a date in January 1900. Set the cell to General or Number and the real figure appears.
A roster shouldn’t need a formula to know how long someone has been here.
Free 14-day trial. No credit card required.
Use =DATEDIF(birthdate, TODAY(), "Y"), which returns the number of complete years and counts only birthdays that have already happened.
The formula is identical to age, just starting from the hire date instead: =DATEDIF(hiredate, TODAY(), "Y") returns completed years of service.
DATEDIF is a compatibility function carried over from Lotus 1-2-3, so Excel does not list it in autocomplete or show argument tooltips. It still calculates correctly if you type the whole thing.
Combine two DATEDIFs with text: =DATEDIF(A2,TODAY(),"Y")&" yrs "&DATEDIF(A2,TODAY(),"YM")&" mos" returns a result like 7 yrs 4 mos in a single cell.
"M" returns the total number of months across the whole span, while "YM" returns only the leftover months after complete years are removed, always between 0 and 11.
Yes, use =YEARFRAC(A2,TODAY()) for a decimal result, optionally wrapped in ROUND. Use it for averaging and charting, not for displaying a person's stated age.
The result cell inherited date formatting from the column. Select the cell, press Ctrl+1, and change the format to General or Number.
The start date falls after the end date, which is nearly always a mistyped year in the source data. Check the row against the original record.
Put the as-of date in a single cell and reference it with an absolute reference such as $B$1 in place of TODAY(), so every row calculates from the same fixed point.
TODAY. NOW includes a time fraction that can shift a boundary result by a day and forces the sheet to recalculate constantly.
Subtract =DATEDIF(A2,TODAY(),"YD") from 365, which gives the days remaining after complete years are stripped out of the span.
Yes, with identical syntax and the same six unit codes, and Sheets does list it in autocomplete with documentation, unlike Excel.
Excel stores dates as day serial numbers, so subtracting two dates gives days. DATEDIF converts that span into years, months or days depending on the unit code you pass it.
Copy the calculated column and use Paste Special > Values over itself, which replaces the live formulas with fixed numbers that will never change again.
Every business starts with a spreadsheet. Updoot is where you scale past it, with tenure and milestones that never need refreshing.
Start Your Free Trial →