Calculating Days Between Two Dates in Excel
The most common way to get the No Of Days Between Two Dates Excel is using the DATEDIF function or simple subtraction. You subtract one date cell from another. That's it. But people always run into trouble because Excel handles dates differently depending on what you actually need to count. Take a cell with your start date and another with your end date. The straightforward formula is =B2-A2. Excel stores dates as serial numbers, so subtracting one from the other just gives you the difference in days. It's that simple when the dates are clean. I once spent forty-five minutes trying to figure out why my calculation was off by three days on a project timeline. Turns out, the source data had some dates in text format and others as actual serial numbers. When Excel subtracts, it silently treats text-formatted dates as zero and gives you a wrong answer without any warning. The fix was running the data through =VALUE() or using Data > Text to Columns to force proper conversion before doing any math.
There's also the DATEDIF function. It's not in the autocomplete menu because Microsoft considers it legacy, but it still works in every version. The syntax is =DATEDIF(start_date, end_date, "unit"). For days, you use "D". For months, "M". For years, "Y". Here's where it gets useful: if you need to know how many complete months sit between two dates regardless of days, DATEDIF with "YM" gives you that, which simple subtraction can't do at all. Another thing people overlook is the difference between excluding the end date and including it. When you do =end-start, Excel counts the gap between the two dates, not the total number of calendar days you were there. If you started Monday and left Tuesday, the formula gives you 1, not 2. For payroll or billing scenarios where you need to include both endpoints, just add 1 to the result. Networkdays is another function worth knowing if you're dealing with business calendars. =NETWORKDAYS(start, end) counts only weekdays, automatically skipping weekends and optionally holidays if you provide a range of holiday dates. The standard version excludes both the start and end date from the count, which catches a lot of people off guard. Add TRUE as the third argument and it includes them.
One nuance that matters: Excel 2003 and earlier use the 1900 date system by default, while later versions can use the 1904 date system depending on the file. They're not compatible. A serial number in a 1900 file will give a different answer than the same number in a 1904 file. This mostly comes up when people share workbooks across Mac and PC or between old and new installations. Check your system under File > Options > Advanced to see which you're on. If you're working with dates outside Excel's supported range, the whole thing falls apart. Excel only handles dates from January 1, 1900 through December 31, 9999. Anything before or after that and you'll get errors or garbage values. In practice, I've seen this bite people importing historical records into spreadsheets and expecting everything to line up. For handling those edge cases, some teams store dates as text strings in YYYYMMDD format and parse them with LEFT and MID functions instead. It's slower but more reliable for archival or cross-referencing work where you might need dates outside the normal range.
Get the Full Details
