To subtract two dates in Excel and return completed whole years, put the earlier date in A2 and the later date in B2, then enter =DATEDIF(A2,B2,"Y") in another cell. For a decimal number of years instead, use =YEARFRAC(A2,B2,1). These calculations help measure ages, service periods, project durations, and other spans between dates; first make sure Excel recognizes both entries as dates.
What You’ll Need
- A version of Excel with the dates you want to compare. These instructions are for Microsoft Excel for Microsoft 365 on Windows; DATEDIF and YEARFRAC are also available in other current Excel versions.
- Two cells containing valid Excel dates. In the examples, A2 contains the start or earlier date and B2 contains the end or later date.
- An empty cell for the result. If you have many date pairs, you can copy the formula down the column after checking the first result.
Step-by-Step
- Arrange the dates in two columns. Put the start date in A2 and the end date in B2. For a list, use the same arrangement on every row, such as start dates in column A and end dates in column B. The order matters: DATEDIF expects its start date to be earlier than or equal to its end date. If you are calculating age, for example, use the birth date as the start and the date of measurement as the end.
- Check that Excel recognizes each value as a date. Select a date cell and look at the formula bar. A real Excel date is stored as a number, even though the cell is usually displayed in a date format. If an entry is left-aligned or changing its number format does not change how it appears, it may be text instead. You can format valid dates from Home > Number Format and choose a date format. This changes the display, not the underlying date value.
- Choose whether you need whole years or a decimal. Completed whole years are appropriate for answers such as “6 years old” or “6 full years of service.” A decimal result is useful when a calculation needs a fraction of a year, such as a duration that will be compared numerically. The two methods use different definitions of the result, so decide before entering a formula.
- Calculate completed whole years with DATEDIF. With the start date in A2 and the end date in B2, enter this in C2:
=DATEDIF(A2,B2,"Y"). For example, if A2 is 15-Mar-2018 and B2 is 10-Nov-2024, the result is 6. The formula counts full anniversaries reached by the end date; it does not round a partial seventh year up. DATEDIF is a supported Excel function, although it may not appear in formula autocomplete, so type it directly if needed. - Calculate decimal years with YEARFRAC when you need a fraction. With the same date layout, enter this in C2:
=YEARFRAC(A2,B2,1). The final argument, 1, specifies the actual/actual day-count basis. For a simple check, if A2 is 1-Jan-2024 and B2 is 1-Jul-2024, the result is about 0.4973 years: the dates are 182 days apart, and 2024 has 366 days. The result is numeric, so you can use it in further calculations. - Format the answer for the reader. DATEDIF returns a number of years, so you can leave it as a number or add a label in a separate heading. For YEARFRAC, select the result cell and use Home > Number Format to choose Number and set the decimal places you want. Avoid applying a date format to the result: Excel may display the number as a calendar date, even though the calculation is correct.
- Copy the formula down for more rows. Select C2 and drag the fill handle at the bottom-right corner of the cell down the results column, or copy and paste it into the cells below. Excel adjusts the relative references for each row, so the next row uses A3 and B3, then A4 and B4. Check a few rows, especially the first and last, to ensure each formula is paired with the intended dates.
DATEDIF and YEARFRAC answer slightly different questions. DATEDIF with the “Y” unit returns only completed years, which is usually what people mean by subtracting dates “in years.” YEARFRAC returns a fraction based on the selected day-count convention. A decimal such as 6.66 is not the same as 6 full years plus a separately rounded month count; it represents a fraction calculated from the dates and basis.
Common Mistakes
- Subtracting the year numbers only. A formula such as end year minus start year ignores the month and day. For example, 2024 minus 2018 gives 6 even if the anniversary in 2024 has not happened yet. For completed years, use DATEDIF with the “Y” unit instead.
- Dividing the difference by 365. Excel stores dates as serial numbers, so subtracting one date from another gives a count of days. Dividing that count by 365 is a rough estimate, not a dependable whole-year calculation; leap years and the exact date boundaries affect the result. Use YEARFRAC with an explicitly selected basis when a fraction is needed.
- Reversing the dates. DATEDIF returns an error if its start date is later than its end date. Check which column contains the earlier date before copying the formula. If a row can run in either direction, decide whether you want a signed duration or an absolute duration and arrange or handle the inputs accordingly.
- Expecting DATEDIF to round up. DATEDIF with “Y” counts anniversaries that have passed. A span of 6 years and 11 months still returns 6, not 7. If your reporting rule requires rounding, specify that rule separately rather than assuming DATEDIF rounds.
- Using a result cell formatted as a date. The formula may return the right number while the cell displays an unexpected date. Set the result to General or Number using Home > Number Format.
- Assuming every YEARFRAC basis means the same thing. YEARFRAC has several day-count bases. Basis 1 uses actual days and an actual-year convention; other bases use different counting rules. Choose one that fits your calculation rather than accepting a default without checking.
Tips
- Make the unit visible in your sheet. Label the result column “Completed years” for DATEDIF or “Years (decimal)” for YEARFRAC. This prevents a decimal duration from being mistaken for a rounded age or count of full anniversaries.
- Use a consistent date-entry approach. Dates such as 4/5/2024 can mean April 5 or May 4 depending on regional settings. To reduce ambiguity, enter dates in a clear format such as 5-Apr-2024, or use Excel’s date picker or a known regional format. Check the resulting date in the formula bar if the source is important.
- Keep the original dates intact. Put calculations in a separate results column instead of overwriting the source dates. This makes it easier to audit the result and correct an input without rebuilding the formula.
- Use the right level of precision. For ages in completed years, DATEDIF is generally clearer. For analysis that needs fractional years, YEARFRAC is more informative. Do not round a decimal merely to make it look like a whole-year count unless that is the intended reporting rule.
- Account for the measurement date. If you are calculating current age or time since an event, the end date needs to be the date you want to measure through. A fixed date gives a result that stays the same; a changing “today” date requires a formula that references the current date. Consider whether a workbook should update automatically before using such a result in a report.
Troubleshooting
- DATEDIF displays #NUM!. The start date is later than the end date, or one of the date values is outside Excel’s accepted date range. Verify the order and inspect both input cells. DATEDIF does not automatically swap reversed dates.
- The formula displays #VALUE!. One or both inputs may be text rather than date values, or the formula may contain a typo. Re-enter the dates in a recognized date format, check that the function name is DATEDIF or YEARFRAC, and make sure text quotation marks around the DATEDIF unit are straight quotation marks.
- The result looks like a date or an odd number. Select the result and change its format to General or Number through Home > Number Format. This is especially common after copying a formula into a cell that previously had date formatting.
- The result is one year lower than expected. Check whether the end date has passed the anniversary. If the start date is 15-Mar-2018 and the end date is 10-Mar-2024, only five complete anniversaries have passed; the sixth is still five days away. DATEDIF correctly returns 5 in that case.
- YEARFRAC gives a value you did not expect. Check the third argument and the date pair. Different basis values produce different conventions, and reversed dates produce a negative result rather than an absolute duration. If you need a positive span, ensure the earlier date is first and use the basis required by your analysis.
- The formula appears as text instead of calculating. The cell may be formatted as Text, or the formula may begin with an apostrophe. Change the cell to General, then edit the formula and press Enter. Also check that Show Formulas is not enabled; on Windows, Ctrl+` toggles formula display. On Mac, use Control+` if that key combination is available on the keyboard, or turn off Show Formulas from the Formulas tab.
On Mac and Excel for the web, the formulas and date logic are the same. Ribbon labels or their placement can differ slightly, but use the Home tab’s number-format controls to set a result to Number or General. The Windows keyboard shortcut mentioned above may differ by keyboard layout; using the ribbon is a practical alternative.
Frequently Asked Questions
What is the simplest formula to subtract dates in Excel in years?
For completed years, place the earlier date in A2 and the later date in B2, then use =DATEDIF(A2,B2,"Y"). It returns the number of full anniversaries between the dates.
Does DATEDIF round to the nearest year?
No. With the “Y” unit, DATEDIF counts complete years only. A remaining part of a year is not rounded up, so a period just short of an anniversary stays at the lower whole-year count.
How do I get years with decimal places?
With the start and end dates in A2 and B2, use =YEARFRAC(A2,B2,1). The 1 selects the actual/actual basis. Format the result as Number and choose an appropriate number of decimal places.
Can I calculate years and leftover months together?
Yes. With the earlier date in A2 and the later date in B2, enter =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months" to return a text label showing completed years and remaining whole months. For 15-Mar-2018 through 10-Nov-2024, it returns “6 years, 7 months.” This result is text, not a numeric value for further arithmetic.
Why is DATEDIF missing from Excel’s function suggestions?
DATEDIF is supported, but Excel may not show it in autocomplete or the function list. Type the complete formula directly, including the quotation marks around the unit, and press Enter.
Can I subtract dates that are in different directions?
DATEDIF requires the start date to be earlier than or equal to the end date. If some rows may be reversed, review or standardize the date order first. YEARFRAC can return a negative value when the dates are reversed, so confirm that a signed result is what you want.
Related
- How to Quick Repair Excel on Windows: Step-by-Step
- How to Randomize Excel Lists Without Any Duplicates
- How to Put X in an Excel Cell and Fill More Cells Quickly
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Block a Sender in Outlook Permanently: What Actually WorksTo block a sender in Outlook and keep the block in place, add the sender’s email address to…
- How to Sort in Google Sheets: A Practical Sorting GuideTo sort in Google Sheets, select the data you want to organize, open Data, and choose a sort…
- How to Keep the Ribbon Open in Excel on Windows and MacTo keep Excel’s Ribbon open on Windows, select Ribbon Display Options near the upper-right corner of the window,…
- How to Delete Blank Cells in Excel Without Losing DataTo delete blank cells in Excel, select the range, choose Home > Find & Select > Go To…
- Excel Column Categories: Data Types and LabelsExcel does not assign a universal set of “column categories.” A column is a vertical set of cells;…
- Excel Workbook Extension: XLSX, XLSM, and MoreThe standard extension for a modern Excel workbook is .xlsx. Use .xlsm when the workbook needs to keep…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.