The simplest Excel formula for calculating tenure in years is =YEARFRAC(start_date, end_date), which returns a decimal like 3.5 for three and a half years. For whole years only, use =DATEDIF(start_date, end_date, "y"). Both formulas assume your dates are real Excel date values, not text.
Understanding Tenure in Excel
Tenure typically means the length of time an employee has been with a company, or the length of time an asset has been in service. In Excel, you calculate it by subtracting one date from another, but the result depends on how you want to express it: as years, months, days, or a combination. Excel stores dates as serial numbers, so subtracting two dates gives the number of days between them. For example, if cell A1 contains 1/1/2020 and B1 contains 1/1/2023, then =B1-A1 returns 1096 (or 1095 in a leap year) — that’s the raw day count. To turn that into meaningful tenure, you use date functions.
The Core Formulas: YEARFRAC and DATEDIF
The two most common formulas are YEARFRAC and DATEDIF. Each serves a different purpose.
- YEARFRAC returns the fraction of a year between two dates. For example, with start date in A2 and end date in B2,
=YEARFRAC(A2, B2)gives a decimal like 2.5. You can round it with=ROUND(YEARFRAC(A2, B2), 1)to get one decimal place. This is ideal for benefits calculations or prorated amounts. - DATEDIF is a hidden function (it doesn’t appear in the function list but works perfectly). It returns whole units.
=DATEDIF(A2, B2, "y")gives complete years,"ym"gives remaining months after years, and"md"gives remaining days after months. For example, if tenure is 2 years, 5 months, and 10 days,DATEDIFwith “y” returns 2, with “ym” returns 5, and with “md” returns 10.
Example: With start date in A2 (e.g., 3/15/2018) and end date in B2 (e.g., 9/20/2023), =YEARFRAC(A2, B2) returns approximately 5.52 (since 5 years and about 189 days out of 365). =DATEDIF(A2, B2, "y") returns 5.
How to Choose the Right Formula
Your choice depends on the output you need:
- For a single number representing total years (with decimals): use
YEARFRAC. This is common for tenure in HR reports where you might want to average tenure. - For whole years only: use
DATEDIFwith “y”. This is typical for service awards or vesting schedules. - For a text string like “2 years, 3 months”: combine DATEDIF with text. For example,
=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months"yields “5 years, 6 months”. - For days only: simply subtract the dates:
=B2-A2.
If you need to calculate tenure as of today, use TODAY() as the end date: =YEARFRAC(A2, TODAY()). This updates automatically each day.
Step-by-Step: Calculating Tenure with DATEDIF
Assume you have a list of start dates in column A (A2:A100) and today’s date is the end. To get each employee’s tenure in years and months:
- In cell B2, enter:
=DATEDIF(A2, TODAY(), "y") & " years, " & DATEDIF(A2, TODAY(), "ym") & " months" - Press Enter. For a start date of 1/15/2019 and today of 5/10/2024, this returns “5 years, 3 months” (since 2019 to 2024 is 5 years, and January to May is 4 months? Actually, let’s compute: from 1/15/2019 to 1/15/2024 is 5 years, then from 1/15/2024 to 5/10/2024 is 3 months and 25 days, so “ym” gives 3).
- Drag the fill handle down to copy the formula to other rows.
If you want the result as a number of months (e.g., for a loan or subscription), use =DATEDIF(A2, B2, "m") which returns total complete months.
Common Problems and How to Fix Them
Several issues arise when calculating tenure:
- #VALUE! error: This usually means your dates are stored as text. Fix by using
DATEVALUEor by converting text to dates via Data > Text to Columns. - Negative results: If the start date is after the end date, DATEDIF returns #NUM!. Check your date order.
- DATEDIF not showing in autocomplete: It’s a hidden function; type it manually. It works in all Excel versions.
- Leap years: YEARFRAC uses a 365-day year by default (basis 0), which can be slightly off for exact day counts. For actual calendar days, use basis 1:
=YEARFRAC(start, end, 1). - Inconsistent results with TODAY(): If you want a static end date, type the date instead of using TODAY().
Another common misconception is that =YEARFRAC rounds to the nearest whole year. It does not; it returns a decimal. To round, wrap it in ROUND or INT.
Comparing YEARFRAC and DATEDIF
Both functions calculate time between dates, but they differ in output and flexibility:
- YEARFRAC: Returns a decimal, allows different day-count bases (US 30/360, actual/365, etc.), and is useful for financial calculations like accrued interest or prorated salaries.
- DATEDIF: Returns integers for years, months, or days, and is better for human-readable tenure like “2 years, 4 months”. It does not support day-count conventions.
For most HR tenure reports, DATEDIF is more intuitive. For financial prorating, YEARFRAC is the standard. You can also combine them: use YEARFRAC for a numeric tenure and DATEDIF for a text label.
Frequently Asked Questions
How do I calculate tenure in years and months in Excel?
Use =DATEDIF(start, end, "y") & " years, " & DATEDIF(start, end, "ym") & " months". Replace start and end with cell references or dates. This gives a text string like “3 years, 2 months”.
What is the difference between DATEDIF and YEARFRAC?
DATEDIF returns whole units (e.g., 3 years) and can give months or days separately. YEARFRAC returns a decimal representing a fraction of a year (e.g., 3.25). Use DATEDIF for readable text, YEARFRAC for numeric calculations.
Why does my DATEDIF formula return #NUM!?
This happens when the start date is after the end date. Check that your start date is earlier. Also, ensure both cells contain actual dates, not text.
Can I calculate tenure as of a specific date instead of today?
Yes, simply replace TODAY() with a cell reference or a date in quotes, like =DATEDIF(A2, DATE(2024,12,31), "y").
How do I calculate tenure in months only?
Use =DATEDIF(start, end, "m") which returns the total number of complete months. For example, from 1/1/2020 to 6/1/2022 gives 29 months.
Does YEARFRAC count leap years correctly?
By default, YEARFRAC uses a 30/360 basis, which ignores actual days. For actual calendar days including leap years, use basis 1: =YEARFRAC(start, end, 1). This divides the actual number of days by 365 (or 366 in a leap year if the period includes Feb 29).
Alternatives and Advanced Tips
If you need to calculate tenure in a more complex way, consider these alternatives:
- Using NETWORKDAYS: If tenure should only count working days (e.g., for contract duration), use
=NETWORKDAYS(start, end)to get business days. - Using INT and MOD: For a numeric value in years with a decimal, you can also use
=INT((end-start)/365.25)but this is less accurate than YEARFRAC. - Array formulas: To calculate tenure for a whole column at once, use dynamic array functions like
=YEARFRAC(A2:A100, TODAY())in Excel 365, which spills results down automatically. - Formatting as a number: If you use
=YEARFRAC, you can format the cell to show one decimal place via Home > Number > Decrease Decimal.
Remember that dates in Excel are numbers, so you can always perform arithmetic. For example, to get tenure in days, simply subtract. For a more precise decimal year, use =(end-start)/365.25 but note this ignores month boundaries. The built-in functions are more reliable.
Related
- Excel Center Louisville KY: What It Is & How It Works
- Excel Choose Date from Calendar Cell: How to Add a Date Picker
- Excel Center Jobs: What They Are & How to Get One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Compare Data in Excel With VLOOKUP: A Step-by-Step GuideTo compare two lists in Excel, use an exact-match VLOOKUP to check whether each item in one list…
- Function in Snowflake: Definition, Use & ExamplesIn Snowflake, a function is a reusable piece of SQL logic that takes input parameters, performs a defined…
- Excel CONCATENATE With Commas: Formulas and ExamplesTo concatenate two Excel cells with a comma and a space between them, use =A2&", "&B2. For a…
- Unique List From Excel Column: Formulas and OptionsTo create a unique list from an Excel column in Microsoft 365, enter =UNIQUE(A2:A100) in an empty cell,…
- Excel Formula to Check If a Value Is in a ListTo check whether a value in D2 appears anywhere in the list A2:A10, use =COUNTIF($A$2:$A$10,D2)>0. Excel returns TRUE…
- Excel COUNTIF Does Not Contain Specific Text?Yes, you can use COUNTIF to count cells that do not contain specific text by using the wildcard…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.