Subtract Two Dates in Excel: Step-by-Step Guide

Updated 5 November 2026 · Standard formula, shown below

This guide shows you several ways to subtract two dates in Excel to get the number of days, months, or years between them. You’ll learn the basic subtraction formula, the DATEDIF function, and how to handle common issues like time values and text-formatted dates. No prior Excel experience is neededâ€”just a spreadsheet with two dates.

What You’ll Need

Microsoft Excel (2010 or later; the steps work in Excel for Microsoft 365, Excel 2019, 2016, and older versions)
Two cells containing dates (e.g., A1 and B1) or a list of dates in a column
Basic familiarity with entering formulas (clicking a cell and typing)

Step-by-Step

Enter your dates. Put the earlier date in one cell (e.g., A1) and the later date in another (e.g., B1). Make sure they are entered as dates (e.g., 3/15/2024 or 2024-03-15), not as text.
Subtract the earlier date from the later date. In a blank cell, type: =B1-A1 and press Enter. Excel shows the number of days between the two dates. For example, if A1 is 1/1/2024 and B1 is 1/10/2024, the result is 9.
Format the result as a number (if needed). If the result appears as a date (like 1/9/1900), right-click the cell, choose Format Cells, select Number, and set decimal places to 0. This converts it to a plain number of days.
Use DATEDIF for days, months, or years. To get the number of complete months between two dates, use: =DATEDIF(A1,B1,"m"). For complete years, use "y" instead of "m". For days, you can use "d" (same as simple subtraction).
Calculate workdays (optional). To count only weekdays (Mondayâ€“Friday) between two dates, use: =NETWORKDAYS(A1,B1). This excludes weekends. To exclude holidays, add a range of holiday dates: =NETWORKDAYS(A1,B1,Holidays) where Holidays is a named range or cell range.
Handle dates with times. If your cells include times (e.g., 1/1/2024 8:00 AM), the subtraction still works but returns a decimal (e.g., 9.5 for 9 days and 12 hours). To get whole days, wrap with INT: =INT(B1-A1).
Deal with text-formatted dates. If your dates are stored as text (e.g., “January 1, 2024”), Excel may not subtract them correctly. Use =DATEVALUE(B1)-DATEVALUE(A1) to convert text to real dates first, then subtract.

Common Mistakes

Subtracting in the wrong order. If you subtract the later date from the earlier one, you get a negative number. Always put the later date first (B1-A1) to get a positive result.
Result shows as a date. When the result is formatted as a date, it looks like a weird date (e.g., 1/9/1900). Change the cell format to Number.
Using DATEDIF with the start and end reversed. DATEDIF requires the start date to be before the end date. If not, it returns an error (#NUM!).
Ignoring time components. If your dates include times, the subtraction returns a fraction. Use INT to get whole days.
Text dates not recognized. If Excel sees the date as text, subtraction fails. Use DATEVALUE to convert.

Tips

Use the DATEDIF function for months and yearsâ€”it’s hidden but works perfectly. The syntax is =DATEDIF(start_date, end_date, "unit") where unit can be “d” (days), “m” (months), “y” (years), “ym” (months excluding years), “yd” (days excluding years), or “md” (days excluding months and years).
For a quick day count without a formula, select the two date cells and look at the status barâ€”Excel shows the difference in days if you right-click the status bar and enable “Date Difference” (available in newer versions).
To subtract a fixed number of days from a date (e.g., 30 days before a deadline), use =A1-30.
If you need the difference in weeks, divide the day count by 7: =(B1-A1)/7.
Use the EDATE function to add or subtract months: =EDATE(A1, 3) gives the date 3 months after A1.

Troubleshooting

Result is a date, not a number. Select the cell, go to Home > Number Format dropdown, and choose Number.
#VALUE! error. This usually means one or both cells are text. Use =DATEVALUE(B1)-DATEVALUE(A1) or re-enter the dates as proper dates.
#NUM! error with DATEDIF. Check that the start date is earlier than the end date. If not, swap them.
Negative result. You subtracted the later date from the earlier one. Reverse the order.
Dates show as #####. The column is too narrow. Widen the column or change the date format to a shorter one.
DATEDIF not available. DATEDIF is not listed in the function library, but you can type it manually. It works in all modern Excel versions.

Frequently Asked Questions

How do I subtract two dates to get the number of days in Excel?

Simply subtract the earlier date from the later date in a formula, e.g., =B1-A1. Format the result as a number.

Can I subtract dates to get months or years?

Yes, use the DATEDIF function: =DATEDIF(start_date, end_date, "m") for months, "y" for years.

Why does my date subtraction return a date instead of a number?

The cell is formatted as a date. Change the number format to General or Number.

How do I subtract dates and exclude weekends?

Use the NETWORKDAYS function: =NETWORKDAYS(start_date, end_date). It counts only weekdays.

What if my dates are in text format?

Convert them with DATEVALUE: =DATEVALUE(B1)-DATEVALUE(A1).

How do I subtract a date from today’s date?

Use the TODAY function: =TODAY()-A1 to get days since the date in A1.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.