Excel Date Difference: Calculate Days, Months, or Years

How-to basics · Updated November 20, 2026

To find the number of days between two dates in Excel, enter the earlier date in A2 and the later date in B2, then subtract the first from the second in C2. For complete months or years, use DATEDIF instead. These methods work in Microsoft Excel for Microsoft 365 on Windows; the key first step is making sure Excel recognizes both entries as dates.

What You’ll Need

  • An Excel workbook with a start date and an end date in separate cells. For the examples below, the start date is in A2 and the end date is in B2.
  • A blank cell for the result, such as C2. If you are calculating many records, you can fill the formula down the column.
  • Dates entered in a format your computer’s regional settings recognize, such as 3/5/2025 or 5-Mar-2025. The first format can mean March 5 or May 3 depending on those settings, so a month name is often clearer.

Step-by-Step

  1. Enter and check the dates. Put the beginning date in A2 and the ending date in B2. Select each cell and look at the formula bar: a true Excel date is stored as a date serial number, even if the cell displays it in a friendly format. If an entry stays aligned as text or changing its number format has no effect, Excel may not have recognized it as a date. Re-enter it using a familiar date format or convert the text before calculating.
  2. Choose what “difference” means for your task. Basic subtraction returns elapsed days: it does not count both the start and end dates as separate calendar days. For example, a trip from Monday to Tuesday is one elapsed day, but it touches two dates. Decide whether you need elapsed days, inclusive days, complete months, or completed years before choosing a formula. For work schedules, age calculations, or billing periods, that distinction can change the answer.
  3. Subtract the start date from the end date for elapsed days. With the start date in A2, the end date in B2, and the result in C2, enter =B2-A2. Press Enter. For example, if A2 is 1/15/2024 and B2 is 3/20/2024, the result is 65 days: 17 days from January 15 to February 1, 29 days in February 2024, and 19 days from March 1 to March 20, with the date intervals adding to 65. The formula works because Excel stores dates as sequential numbers and subtracts those numbers.
  4. Format the result as a number if it appears as a date. Select C2, then choose Home > Number Format and select General or Number. The result is a count of days, not a date on the calendar. You can also use Ctrl+1 on Windows to open Format Cells, select the Number category, and choose a format. On Mac, use Command+1 to open Format Cells; the menu and ribbon labels can vary slightly by Excel release. Excel for the web has number-format controls on the Home tab as well.
  5. Count both dates when the range is inclusive. With A2 as the first date and B2 as the last date, enter =B2-A2+1 in C2. For January 15 through March 20, 2024, this returns 66 calendar dates, because it includes both January 15 and March 20. Use this for a date range where both boundary dates count, such as days covered by an event. Do not add 1 automatically if you need elapsed time; that would overcount by one.
  6. Calculate complete months with DATEDIF. With the start date in A2, the end date in B2, and the result in C2, enter =DATEDIF(A2,B2,"m"). This returns the number of whole months completed between the dates, not a rounded estimate. For January 15 to March 20, 2024, the result is 2: two complete monthly anniversaries have passed. DATEDIF is a supported Excel function, although it may not appear in the formula autocomplete list, so type it carefully.
  7. Calculate completed years when you need age or service length. With the same date layout, enter =DATEDIF(A2,B2,"y") in C2. This returns full years completed, not the difference between the year numbers. For someone whose birth date is June 10, 2000, and whose age is checked on June 9, 2024, the result is 23; it becomes 24 on June 10. Use the actual start and end dates rather than subtracting year numbers, which can be off by one before the anniversary.
  8. Fill the calculation down for additional rows. If each record has its start date in column A and end date in column B, first enter the appropriate formula in C2. Select C2 and drag the small square at the lower-right corner of the cell down the rows, or copy and paste it. Excel adjusts the row references for each record. Check a few results at the bottom and middle of the list to make sure the date columns are consistent and that formulas have not been overwritten by values.
  9. Keep the result useful and readable. Add a heading such as “Elapsed days” or “Complete years” above the results so nobody mistakes one measure for another. If you want a sentence that displays the day count, keep the calculation in a separate numeric column and build the label in another cell; text-formatted results are harder to sum, filter, or use in later calculations.

Common Mistakes

Reversing the dates. The formula must subtract the start date from the end date. If B2 is earlier than A2, ordinary subtraction returns a negative number. That may be correct for a backward interval, but it often signals that the dates were entered in the wrong columns. Check the source dates rather than hiding the negative sign.

Confusing elapsed and inclusive days. Subtraction counts the boundary between dates, not both labels on a calendar. A one-day event on a single date has zero elapsed days from its start to itself, but it covers one calendar date. Add 1 only when the question explicitly calls for counting both endpoints.

Using the wrong DATEDIF unit. The unit is supplied as text in quotation marks. The unit “m” means complete months and “y” means complete years; they do not mean calendar-month or calendar-year labels touched. DATEDIF also accepts units such as “ym” for months after complete years, but do not assume separate month and day pieces always add cleanly in every unusual month-end case. If a contract or policy defines its own counting rule, follow that rule.

Formatting a day count as a date. Excel may display a result such as 65 as a date serial if the result cell has a Date format. Change the cell to General or Number. Conversely, displaying the result as text can prevent later arithmetic, so keep numeric calculations numeric.

Assuming a displayed date proves the cell contains a date. Text can look like a date but fail in subtraction, causing #VALUE!. A cell’s alignment alone is not definitive because formatting can change alignment. Test by changing the cell format to General or use a clean, unambiguous date entry; if it remains text, convert it rather than merely changing its appearance.

Tips

Use a four-digit year in entries and labels to reduce ambiguity, especially when sharing a file across regions. An entry such as 5-Mar-2025 is clearer than 3/5/25. Excel’s displayed date order depends on regional settings, so the same-looking slash-separated date may be interpreted differently on another computer.

If the cells include times as well as dates, subtraction returns a fraction of a day as well as whole days. A result of 1.5 means one day and twelve hours. Format the result as Number to see the fraction, or use an appropriate elapsed-time format such as [h]:mm when you want total hours and minutes. Square brackets allow hours to exceed 24.

For recurring reports, use an Excel Table: select the data and choose Insert > Table. Formulas entered in a table column generally fill down automatically for new rows. Give date columns clear headings such as Start Date and End Date, and avoid blank rows inside the data so it is easier to sort and filter.

Keep the original dates in separate columns and put the calculation in its own column. This makes it simple to check the inputs, revise the counting method, or compare elapsed days with completed months. If the dates come from another system, preserve a copy of the imported values before converting them.

Troubleshooting

The result is #VALUE!: at least one input may be text, blank in an unexpected way, or otherwise not a valid date. Re-enter the values as dates and inspect the formula bar. If a column contains imported date text, use Data > Text to Columns and follow the prompts to convert it, choosing the appropriate date order when offered. Always check a sample before applying a conversion to the full column.

The result is a date rather than a day count: select the result cell and change Home > Number Format to General or Number. The calculation may be correct; the cell’s display format is what makes it look wrong.

The result is negative: confirm which cell holds the start date and which holds the end date. If your dates legitimately run backward, decide whether a negative interval is useful. If you only want the absolute distance regardless of order, you can use ABS. With the two dates in A2 and B2 and the result in C2, enter =ABS(B2-A2). This removes the sign, so it should not be used when direction matters.

DATEDIF returns an unexpected whole-month or whole-year count: check the day of the month as well as the year and month. DATEDIF counts completed anniversaries, so dates near month-end may not reach the next full month until the corresponding date boundary is passed. Confirm that you selected “m” or “y” and that the start date is not later than the end date.

A formula appears as text or does not update: set the result cell to General, then re-enter the formula. Check that it begins with an equals sign and that the cell does not have a leading apostrophe. If Excel is showing formulas throughout the sheet, toggle Show Formulas from the Formulas tab or press Ctrl+` on Windows. Mac shortcut availability differs by keyboard, so use the ribbon command if that key combination is unavailable.

The formula is rejected after pasting: Excel normally uses commas between function arguments in Microsoft 365, but regional settings can require semicolons. If your Excel expects semicolons, replace the argument separator in a pasted DATEDIF formula. Date parsing also follows regional settings, so use an unambiguous date entry and confirm the displayed value before trusting the result.

Frequently Asked Questions

Can I calculate the difference between dates in Excel without a function?
Yes. If A2 contains the start date and B2 the end date, subtract A2 from B2. Excel stores recognized dates as serial values, so the result is the elapsed number of days. Use DATEDIF for completed months or years.

How do I count the start and end date?
For an inclusive count, use the ordinary end-minus-start calculation and add 1. This counts a date range such as April 8 through April 8 as one calendar date rather than zero elapsed days.

Does DATEDIF work in Microsoft 365?
Yes. DATEDIF works in Microsoft 365, even though Excel may not suggest it while you type. Enter its arguments and unit carefully, and use dates in the correct start-to-end order.

How can I find someone’s age in years?
Use DATEDIF with the birth date as the start date, the date to check as the end date, and the “y” unit. It returns completed years, so the person does not gain another year until their birthday.

Why does subtracting two dates return a decimal?
One or both cells probably include a time. Excel stores times as fractions of a day, so subtraction includes hours and minutes. Use a whole-day display or remove the time component only if the task requires dates alone.

Can I calculate dates in Excel for the web or on a Mac?
Yes. Date subtraction and DATEDIF work in current Excel for the web and Excel for Mac as well as Windows. Ribbon layouts can differ slightly, but the formulas are the same; keyboard shortcuts and regional date or argument separators may vary.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.