Level 7 · Days between dates with DATEDIF

Jason’s question is a classic operations metric: how long does a return take to come back, and which ones broke the policy? Because dates are numbers, the days between two dates is a subtraction. The catch is in the data: the order date has a time on it.

Subtracting dates

The order in row 2 was placed on 2024-05-24 at 07:19 (C2) and came back on 2024-07-01 (D2). Try:

=D2-C2

The result is 37.70, not 38. A date without a time sits at midnight. The order came in at 7:19 in the morning, so between the two lie 37 full days plus the 0.70 of a day that was left of May 24. Format the cell with no decimals and it shows 38, which looks right, while every formula built on it still works on 37.70. Ordered on the 24th, back on the 1st: your colleagues count that as 38 days, so drop the time first.

One more surprise: Excel sometimes formats the result of a date subtraction as a date, and the cell shows something like 1900-02-06. The number underneath is fine; set the cell’s format to Number or General.

DATEDIF counts whole days

F2   =DATEDIF(C2,D2,"d")

DATEDIF(start, end, unit) takes the earlier date first and ignores the time of day. The unit says what to count:

Unit	Counts	C2 to D2
"d"	whole days	38
"m"	whole months	1
"y"	whole years	0

"m" and "y" are what you would use for tenure or a customer’s age in months. Two other ways give the same 38 days: =D2-INT(C2) (INT cuts the time off the order date) and =DAYS(D2,C2) (the end date comes first there). Excel doesn’t list DATEDIF in its formula autocomplete, an old quirk, but it works in every version and in Google Sheets.

If the start is later than the end, DATEDIF returns #NUM!. On real data that is a useful alarm: a return logged before its order is a data-entry error.

Outside the window or within it

The policy is “within 30 days”, and the window is in J1. A return on day 30 is within it; day 31 is not. So the test is >:

G2   =IF(F2>$J$1,"outside","within")

Fill both columns down to row 35. July has 34 returns in this sheet and 5 of them came back after the window. One sits exactly on the edge: row 20, a Kitchen return 30 days after its order, which is within. If you count 6 outside, check the operator.

Row 20 is also a reminder to read the columns: its reason is late, which means the delivery arrived late, not that the return did. Name your own columns so nobody confuses the two.

Other date parts you’ll meet

Timestamps hold more than a date. =HOUR(C2) returns 7, the hour the order was placed, handy for “when do people shop” questions. =WEEKDAY(D2,2) returns 1 for Monday through 7 for Sunday (July 1, 2024 was a Monday), and =NETWORKDAYS(C2,D2) counts only Monday to Friday, 27 here, for policies stated in business days.

Common mistakes

• Plain subtraction on a timestamp. It gives fractional days. Use DATEDIF, or INT on the timestamp.
• The dates in the wrong order. DATEDIF(D2,C2,"d") returns #NUM!; =C2-D2 returns a negative number.
• >= instead of >. Day 30 is within a 30-day window; >= flags row 20 by mistake.
• Typing 30 into the formula. Point at $J$1, so a policy change is one cell.