Excel and Google Sheets how-to
Calculate years of service from a start date
Ana started on 15 March 2019. How many full years has she worked? DATEDIF counts them, and the same formula works out an age from a date of birth.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Start date | Years | As of | |
| 2 | Ana | 2019-03-15 | 7 | 2026-10-20 | |
| 3 | Raj | 2021-11-01 | 4 | ||
| 4 | Kim | 2024-06-30 | 2 | ||
| 5 | Lee | 2010-01-04 | 16 | ||
| 6 | Sam | 2026-02-09 | 0 |
Sample data. Start dates in B, the date to count up to in E2, the formula in C2 is copied down.
The formula
Put the date you count up to in one cell (E2 here, 20 Oct 2026), click the first Years cell and type:
=DATEDIF(B2,$E$2,"Y")C2 shows 7: Ana started on 15 Mar 2019, so she has 7 complete years by 20 Oct 2026. Copy it down and Raj shows 4, Kim 2, Lee 16 and Sam 0.
- B2 is the start date.
- $E$2 is the end date. The dollar signs keep it on E2 when you copy the formula down.
- "Y" asks for complete years. A year only counts once the anniversary has passed, so Raj (started 1 Nov 2021) still shows 4 until 1 Nov 2026.
Count up to today
Use TODAY() as the end date and the years go up by themselves on each anniversary:
=DATEDIF(B2,TODAY(),"Y")On 20 Oct 2026 it gives 7 for Ana, the same as above. Put a date of birth in B2 and the same formula gives an age.
Years and months
"YM" counts the months left over after the whole years. Join both parts into one line:
=DATEDIF(B2,$E$2,"Y")&" years, "&DATEDIF(B2,$E$2,"YM")&" months"Ana shows 7 years, 7 months and Raj shows 4 years, 11 months.
Years with a decimal
For an average or a pay scale you may want part years too. YEARFRAC gives them, and ROUND keeps one decimal:
=ROUND(YEARFRAC(B2,$E$2),1)Ana has 7.6 years. Use DATEDIF when you need whole years only.
Total months
"M" counts complete months from start to end:
=DATEDIF(B2,$E$2,"M")Ana has 91 months (7 years and 7 months).
An error, or a date instead of a number
DATEDIF gives an error (#NUM! in Excel) when the start date is after the end date, for example a future start date or the two dates swapped. Show a blank instead:
=IF(B2>$E$2,"",DATEDIF(B2,$E$2,"Y"))Ana still shows 7; a start date after E2 leaves the cell empty. If the result looks like a date in January 1900, the cell has a date format: change it to Number. Microsoft keeps DATEDIF for older Lotus 1-2-3 workbooks and advises against its "MD" unit, so stick to "Y", "M" and "YM". DATEDIF works in Excel 2016 and later, Microsoft 365 and Google Sheets.
Templates with the math already done
Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.
- Leave and PTO Tracker 2027Vacation, sick days and other time off for up to 40 people, in working days.
- Employee Schedule TemplatePlan your team's shifts week by week with shift codes or times.
- Resume Template (Word)3 ATS friendly resume designs, each with a cover letter and references page.
More how-tos
- Find a price by its code with XLOOKUPType a product code, get its price back from your price list. One formula, no helper columns.
- Total each category with SUMIFSAdd up all the Fuel, all the Office, all the Meals: one formula per category, and more rules when you need them.
- Check two conditions at once with IF and ANDFree shipping only when the order is 50 or more and ships in Canada: one IF, one AND, done.
- Add leading zeros to numbersType 00021 and Excel or Google Sheets shows 21, because to them it is just the number 21. Here is how to get the zeros back, and keep them.
- Fix #DIV/0! in an averageAn average of nothing is a division by zero, so AVERAGE shows #DIV/0! until the first number arrives. One IF fixes it.
- Count rows that meet two conditions with COUNTIFSHow many of Ana's deals were 500 or more? COUNTIFS answers it in one cell, and takes as many rules as you need.
- Calculate percentage changeSales went from 400 to 500. That is a 25% increase, and one short formula works it out for every row.
- Combine first and last name into one cellFirst names in one column, last names in the next, and you need "Ana Lopez" in one cell. The & sign joins them.
- Find duplicates in a list with COUNTIFA mailing list where some people signed up twice. One formula shows how many times each email appears, so the repeats stand out.
- Split a full name into first and last nameYour list has "Ana Lopez" in one cell and you need Ana and Lopez in two. Two short formulas find the space and split there.
- Free pricing calculatorsPrice a job from your real costs and margin. No sign up.