Count Days Between Two Dates Excel

How to actually count days between two dates in Excel

The simplest way to get the difference between two dates is just to subtract one cell from another. Put your start date in A1 and your end date in B1, then type =B1-A1 in C1. Excel stores dates as serial numbers under the hood — January 1, 1900 is 1, January 2 is 2, and so on — so subtraction gives you the raw day count immediately. Format the result cell as General or Number if it's showing a date instead of a plain integer. But here's where it gets messy. If you subtract B1 from A1 and A1 is earlier, you get a negative number. I once had to reconcile a vendor's invoice that was off by exactly 365 days because someone had entered the dates in the wrong order and the spreadsheet was pulling from a column full of negative values. The fix was wrapping it in ABS(): =ABS(B1-A1). Simple enough, but it cost me two hours of cross-referencing spreadsheets to catch.

Count Days Between Two Dates Excel

There are three main approaches depending on what you actually need. The first is raw subtraction as described above — this counts all days, including weekends and holidays, and includes both the start and end date in the count. If your start date is March 1 and your end date is March 3, subtraction gives you 2. That's the number of day-boundaries crossed, not the number of calendar days you'd tick off on a hand calendar. The second approach is NETWORKDAYS. Use =NETWORKDAYS(A1,B1) and it returns only weekdays, skipping Saturdays and Sundays. You can optionally pass a third argument listing holiday dates: =NETWORKDAYS(A1,B1,holiday_range). This is the one most people actually want when they're calculating work hours, billable days, or project timelines. The tradeoff is that it only works in Excel 2007 and later, and the holiday list has to be actual Excel serial dates, not text strings formatted as dates. The third is NETWORKDAYS.INTL, which lets you define which days are weekends. The default weekend code is 1 (Saturday and Sunday), but if your company operates Saturday shifts and closes Monday through Wednesday, you'd use code 11: =NETWORKDAYS.INTL(A1,B1,11). There are 17 weekend codes total. I learned about this the hard way when a construction client needed timelines calculated around their three-day weekend, and our original formulas were returning wildly inflated day counts because we assumed a standard Monday-to-Friday workweek.

Then there's DATEDIF, a function that technically still works but isn't documented in Excel's help system. It's been around since Excel 2000 and behaves differently from the other two. =DATEDIF(A1,B1,"d") gives you days, "m" gives months, and "y" gives years. The catch is that DATEDIF counts complete intervals, not boundaries crossed. If A1 is January 31 and B1 is March 1, DATEDIF with "d" returns 29 in a non-leap year, not 30. That's because internally it calculates month-by-month and the remainder days get thrown out. I've seen this bite people reporting contract durations — they'd get one day short every time the start date was the last day of a month.

Things that break your day counts

The biggest pitfall isn't the formula itself, it's the input. If one of your date cells is actually stored as text, Excel won't subtract it properly. You'll get a #VALUE! error or, worse, a silently wrong number if Excel's AutoConvert feature kicks in inconsistently across rows. Check by selecting the cell and looking at the formula bar — if there are no quotes around the date but it doesn't behave like a date, run =ISNUMBER(A1). If it returns FALSE, convert it with =DATEVALUE(A1) or re-enter it as an actual date. Another issue is the 1900 date system versus the 1904 system. Mac versions of Excel from before 2008 sometimes defaulted to 1904, which shifts every serial number by 1,462 days. If you're importing files between Mac and PC and your day counts are off by roughly four years, this is probably why. Go to File > Options > Advanced and check the date system setting. Most people don't know this exists until their numbers look insane. Leap years also trip people up. NETWORKDAYS handles them automatically, but manual calculations using (end-start)/365 will drift. Over a ten-year span you're looking at a one-day error minimum. Don't use simple division for anything longer than a year.

A realistic workflow

Here's what I usually do when someone sends me a spreadsheet with date columns and asks for day counts. First I verify the date format across the entire range. I'll select the column, go to Home > Number Format > Date, and confirm the serial numbers look right. Then I build the formula in a new column. For straightforward elapsed days I use =B2-A2. For business days I use =NETWORKDAYS(A2,B2). I always add a conditional formatting rule that highlights negative results in red — it catches swapped date columns faster than anything else. If the dataset is large, say over 50,000 rows, NETWORKDAYS can slow things down noticeably because it recalculates on every change. In those cases I've used a helper column with simple subtraction and then applied the weekday exclusion through a separate lookup table. It's more steps but the calculation time drops from several seconds to under a second. One more thing nobody mentions: if your date range spans a daylight saving time change and you're doing anything with timestamps rather than plain dates, the arithmetic is still correct for day-counting purposes. Excel doesn't care about time zones in its date serial numbers. But if you're exporting to a system that does, you might need to adjust afterward. I lost a day on a client deliverable once because the raw count was right but the destination platform interpreted the boundary date as a different UTC day.