Calculating the gap between two dates in Excel is one of those things everyone needs to do and most people mess up at least once.
The straightforward method uses the subtraction operator. You place the later date in one cell, the earlier date in another, and subtract them. Excel stores dates as sequential serial numbers, so simple arithmetic gives you the count of days. It works immediately, no function lookup required. Enter your end date, enter your start date, type the formula, and you're done. Here is the basic syntax you will use most of the time: =B2-A2
Cell B2 holds your end date and cell A2 holds your start date. The result is a plain number representing days. If you want it formatted, change the cell format to General or Number. By default Excel may show the result as a date again, which is confusing if you do not know why. But there are cases where simple subtraction gives misleading answers, and this is where people lose hours they do not have. I was working on a report last year for a logistics client who needed turnover time calculated across shifts. Their spreadsheet had dates pulled from three different ERP systems. Two of those systems output dates as text strings in completely different formats. When I ran the subtraction formula, half the results were zero and the other half returned errors. It took me twenty minutes to realize the issue was not the formula at all. It was the data type. Converting everything with the DATEVALUE function fixed it, but only after I spotted the hidden problem in column formatting. This is the kind of thing that quietly eats your afternoon.
Another common trap: using DATEDIF. It exists in Excel even though Microsoft never officially documented it. The function syntax is =DATEDIF(start_date,end_date,"unit"). The unit argument controls what you get back. Use "d" for days, "m" for months, "y" for years, or combinations like "md" for remaining days after removing full months. It is useful when you need a breakdown rather than a single total. But DATEDIF has a notorious bug with the "md" unit when the end day is numerically smaller than the start day. It returns incorrect values. I learned this the hard way when cross-checking payroll calculations for a temp staffing agency. The discrepancies appeared only in months where the 31st was involved and the following month had fewer days. I switched to a nested IF formula instead and never looked back. If you need business days only, excluding weekends, use the NETWORKDAYS function. It automatically skips Saturdays and Sundays. You can also feed it an optional range of holiday dates so those are excluded too. Syntax looks like this: =NETWORKDAYS(start_date,end_date,[holidays])
Get the Full Details
This is essential for things like project timelines, invoicing cycles, and SLA tracking. A standard subtraction will give you five days for a Tuesday-to-Friday span, but NETWORKDAYS gives you three because weekends do not count. Know which metric your business actually needs before you pick a formula. There are also edge cases worth noting. If either cell contains a time component mixed with the date, subtraction still works but the result includes fractional days. A difference of 0.5 means twelve hours. Format the result as a number, not a date, to see the decimal. Another issue: empty cells or zero values. If one date is blank, Excel treats it as zero, which is January 1, 1900. Your result will be wildly wrong. Always validate inputs first. A quick =IF(OR(A2="",B2=""),"",B2-A2) keeps your sheet clean. Sometimes you need the absolute value regardless of which date comes first. Wrap your subtraction in ABS:
=ABS(B2-A2) This prevents negative numbers when users enter the start and end dates in reverse order. It is a small thing but it saves a lot of frustration in shared spreadsheets. Here are some concrete examples using sample data:
Start date in A2: 03/15/2024
End date in B2: 04/02/2024
=B2-A2 returns 18
=DATEDIF(A2,B2,"d") returns 18
=NETWORKDAYS(A2,B2) returns 13, because weekends fall between those dates. The bottom line is that subtraction handles most everyday cases fine, but you need to understand what happens when dates are stored as text, when times are attached, when holidays matter, and when DATEDIF silently produces wrong results. Pick the right tool for the actual requirement instead of reaching for the first function you remember. A twenty-minute audit of your data types before writing any formula will save you from going back and correcting fifty rows later. If you want a reference file with these formulas prebuilt and ready to copy, the download is linked below. It includes example datasets showing each method side by side so you can see the differences without setting anything up yourself.
