How To Subtract Dates In Excel

Why Subtracting Dates Feels Broken Until It Doesn't

Excel stores dates as serial numbers. January 1, 1900 is 1. Today's date is some five-digit number depending on your system. When you subtract one date from another, Excel subtracts the underlying serial numbers. The result is a plain integer representing the number of days between them. That sounds simple enough until you actually try it in a real spreadsheet and get a result that makes no sense at first glance, or worse, a #VALUE! error because someone formatted a cell as text instead of keeping it as a date.

How To Subtract Dates In Excel Using Simple Subtraction

The most straightforward approach is just subtracting two cells that contain valid dates. If A2 holds your start date and B2 holds your end date, the formula =B2-A2 returns the day count between them. That is it. The cell needs to remain formatted as General or Number to display the result correctly. If you leave the default date format on the result cell, you will see another date instead of a number, which is almost never what you want. One thing that trips people up constantly: Excel treats the result as a number unless you tell it otherwise. So =B2-A2 can be used directly in further calculations like =ROUND((B2-A2)/30,2) to get approximate months, or multiplied by hourly rates if you are computing labor costs across date ranges. I have seen junior analysts try =DATEDIF(A2,B2,"m") when they only needed a day count difference, which adds unnecessary complexity and creates its own set of problems.

The DATEDIF Function And Why You Should Use It Carefully

DATEDIF is not listed in Excel's function wizard. It does not appear under Insert Function. Microsoft considers it a legacy function that exists only for backward compatibility with Lotus 1-2-3, yet it remains fully functional across every version of Excel released since. That alone should make you suspicious of blindly trusting it. The syntax is =DATEDIF(start_date,end_date,unit). The unit argument accepts "d" for days, "m" for months, and "y" for years. There are also compound units like "ym", "yd", and "md" that calculate differences while ignoring certain components. For example, "ym" gives you the month portion of a difference while ignoring years and days. Here is the problem I ran into last year that took me three hours to track down: I was building a contract management sheet where DATEDIF was returning wrong results for end dates that fell in February. The formula =DATEDIF(A2,B2,"ym") returned 1 instead of 0 when comparing January 31 to February 28 of the same year. The issue is that DATEDIF calculates based on whole months elapsed, and the internal algorithm handles month-end dates differently than most people expect. Excel essentially rounds down during intermediate calculations, and February's shorter length exposes the edge case immediately.

The workaround I ended up using was a combination approach. For month differences, I switched to =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)-(DAY(B2)

DAY(A2)). It is longer but it behaves predictably across every month length and leap year scenario. For day counts, I just went back to =B2-A2 and formatted the result as a number. The DATEDIF function is fine for clean date pairs where both dates fall on the same day of the month or where the month alignment is straightforward, but it is not reliable for boundary conditions.

What Goes Wrong Most Often

The top issue I see in production spreadsheets is text-formatted dates. If a date was imported from a CRM system, pulled from a CSV, or typed in by a user who hit Enter too fast, the cell might look like a date but Excel stores it as text. The subtraction will silently return a #VALUE! error. You can catch this before it breaks anything by wrapping the formula in IFERROR or by testing with =ISNUMBER(A2). The ISNUMBER check returns FALSE for any date stored as text, which is a useful diagnostic you can apply to an entire column in seconds. Another common failure mode involves the 1900 date system bug. Excel incorrectly treats 1900 as a leap year, meaning it believes February 29, 1900 actually existed. Dates before March 1, 1900 will be off by one day if you are working with historical records or legacy data that uses those dates. If your dataset has pre-1900 entries, subtract them using a different calculation method or convert the dates through a reference table that accounts for the offset. This affects very few users, but when it hits you, it looks like a random bug and you will waste time searching for the cause. Time values embedded in date cells are a third frequent source of confusion. If A2 contains 3/15/2024 2:00 PM and B2 contains 3/16/2024 8:00 AM, the subtraction =B2-A2 returns 0.75, not 1. That is because Excel includes the time portion in the serial number. The practical fix is to wrap both dates in the INT function: =INT(B2)-INT(A2), which strips the time component and leaves you with clean whole days. I use this pattern in virtually every project where time of day might be attached to a date field, and I usually standardize it with a helper column that forces dates into midnight-only serial numbers so downstream formulas do not need to remember to apply INT themselves.

When Simple Subtraction Is Not Enough

There are scenarios where subtracting two dates directly gives you a number that is technically correct but operationally useless. Suppose you need to exclude weekends from your count, or you need business days only for a project timeline. The raw subtraction returns calendar days, which overstates the actual working time in most cases. For business day calculations, use =NETWORKDAYS(start_date,end_date) and optionally pass an array of holiday dates as the third argument. This is how most finance teams compute payment terms and delivery windows, and the difference between calendar days and business days can be substantial on longer ranges. For a pure working-day count that also respects a custom holiday list stored in another range, the formula becomes =NETWORKDAYS(A2,B2,holiday_range). I usually keep the holiday list on a separate sheet labeled Data so that it can be updated once per year and referenced without hardcoding individual dates. Updating a holiday list is far less error-prone than editing twenty-seven NETWORKDAYS formulas after a company changes its December closure policy. If you need to count only specific weekdays, like business days that are not Mondays, NETWORKDAYS does not handle that directly. You can approximate it by subtracting the count of excluded days from the total, but the approximation degrades quickly when holidays overlap with the excluded weekday. The reliable path is to generate a column of sequential dates and use SUMPRODUCT with WEEKDAY to flag the days you want to count. It is more setup work, but it produces accurate results even in edge cases.

A Quick Reference For The Most Common Setups

For basic calendar day subtraction, =B2-A2 with the result formatted as a Number. For month differences that ignore year boundaries, =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)-(DAY(B2)

DAY(A2)). For business days including a holiday list, =NETWORKDAYS(A2,B2,holidays). For day counts that strip embedded times, =INT(B2)-INT(A2). Each of these handles a distinct real-world situation, and using the wrong one for your data is why people complain that Excel date subtraction is unreliable. One final note on performance. If you are subtracting dates across tens of thousands of rows, array formulas and volatile functions will slow the workbook noticeably. NETWORKDAYS is not volatile, but it recalculates every time any cell changes because Excel cannot isolate which rows need updating. On large datasets, converting the holiday list to a static reference or using a Power Query-based lookup structure instead of a live formula array will cut recalculation time from several seconds down to under a second. I learned that the hard way on a 14,000-row schedule file that took forty seconds to open before I restructured the calculation layer.