Excel Difference In Days

Getting the Days Between Two Dates in Excel

Subtract one date from another and Excel handles the rest. Dates in Excel are stored as serial numbers, so the math is straightforward. Put your start date in one cell, your end date in the next, and in a third cell type minus. That is the core of it. DATEDIF is the other tool people reach for, and it has its own quirks. The function takes three arguments: the start date, the end date, and a unit code. Unit codes like "D" give you whole days, "M" gives you months, and "Y" gives you years. It sounds simple until you try to use it on anything that crosses month boundaries with irregular day counts, which is exactly where most people hit a wall.

Excel Difference In Days: Simple Subtraction Method

Here is the subtraction approach. Let us say cell A2 holds your start date and cell B2 holds your end date. You want the difference in days. Put this in C2: =B2-A2 That returns a number. Excel stores dates as integers counting from January 1, 1900, so subtracting one date from another gives you the exact day count between them. Format the result cell as Number or General if it shows up as a date instead. That happens more often than you would think because Excel auto-formats cells based on the data you first entered.

There is a small but important detail here. If your end date is earlier than your start date, you get a negative number. This is fine if you are tracking durations where direction matters, but it breaks anything expecting a positive value. Wrap it in =ABS(B2-A2) if you need the absolute difference every time. I do this in every sheet I build. Makes the downstream calculations less fragile.

Using DATEDIF for More Control

The =DATEDIF(A2,B2,"D") formula does the same subtraction but with a different internal logic. The key difference is that DATEDIF handles partial months and years differently when you use "M" or "Y" as the unit. For plain days ("D"), the result matches subtraction in almost every case. Where DATEDIF actually diverges is when you use the "MD" unit. This gives you the difference in days after removing complete months. It sounds useful. It is not. The "MD" calculation has known bugs around leap years and certain month combinations. I stopped relying on it years ago after spending three hours debugging a headcount model where "MD" was returning zero for dates that clearly had a gap. For counting full months between two dates, use this instead:

=YEARFRAC(A2,B2)*12 Then wrap it in INT if you want whole months. YEARFRAC calculates based on a 360-day year by default in Excel, which changes the output slightly compared to actual calendar days. Most people do not notice the difference. If you are tracking project timelines against contracts, however, the discrepancy can add up to a day or two over several months.

A Real Problem I Ran Into

Three years ago I was building a payroll reconciliation sheet for a company with employees across three time zones. The dates were being pulled from an external system in mixed formats. Some cells had dates stored as text like "03/15/2022" without leading zeros, others were proper Excel serial dates, and a few had trailing times embedded like "2022-03-15 14:32:00". When I ran a standard =B2-A2 calculation, about forty percent of the rows returned either a decimal instead of a whole number or a completely wrong value because the text dates were being treated as strings. The fix was to normalize everything first. I added a helper column with this formula: =DATEVALUE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"/","-")," ",""))

That converts text dates into proper Excel serial numbers regardless of whether they had slashes, dashes, or spaces. Then my subtraction worked on the second column. Took me maybe twenty minutes to implement once I figured out what was going on, but the initial diagnosis took longer than I care to admit. I now include this normalization step in every template I hand off.

Edge Cases and Things That Go Wrong

Time components in your dates will throw off day counts if you are not paying attention. If A2 contains "2023-01-15 23:00" and B2 contains "2023-01-16 01:00", simple subtraction gives you 0.0833 days instead of 1. You can strip the time portion with =INT(B2)-INT(A2) or wrap each cell in the INT function before subtracting. I always do this. It is one of those things you forget until your numbers look wrong and you cannot figure out why for twenty minutes. Leap years also deserve a mention. Excel handles them correctly in subtraction because it uses serial numbers. But if you are using DATEDIF with the "Y" unit to calculate full years between two dates spanning a February 29, you can get unexpected results. For example, the difference between 2020-03-01 and 2021-02-28 returns 0 years with DATEDIF because the end date has not yet reached the anniversary month-day. This is documented behavior, not a bug, but it catches everyone at least once. Another common issue: blank cells. If either the start or end date is empty, subtraction gives you zero or an error depending on context. DATEDIF returns a #VALUE! error with blank inputs. If your data comes from a source that occasionally leaves dates missing, add error handling:

=IF(OR(A2="",B2=""),"N/A",B2-A2) This prevents the whole column from breaking when someone forgets to fill in a field. I build this into every template I ship. There is a hard limit to how far back Excel date math works reliably. The Excel 1900 date system starts at January 1, 1900 (serial number 1), and goes through December 31, 9999. Anything before 1900 requires the 1904 date system, which shifts the base by four years. If you are working with historical data from before 1900, standard subtraction will give you errors or wildly incorrect values. There is no clean workaround inside Excel itself. You either pre-process the data in another tool or accept that Excel is not the right instrument for that job.

Performance also degrades noticeably when you apply these formulas across hundreds of thousands of rows. A simple subtraction formula on a million-row dataset will make Excel sluggish. I have seen it happen. The solution is to convert the formulas to values once the calculation is complete. Select the column, copy, then paste values over the top. The data stays the same. The workbook shrinks and loads faster.