Excel guide · 4 min read

IF a date is before or after another date in Excel

Q: How do I check if a date is before another date in Excel?

Compare the cells: =A2<B2 returns TRUE if the date in A2 is earlier. Wrap it in IF for a label: =IF(A2<B2,"Before","Not before").

Q: How do I write an IF formula for a date before today?

Use TODAY(): =IF(A2<TODAY(),"Past","Today or later"). TODAY() recalculates every day, so the result changes over time; add a blank check if the column has empty cells.

Q: Why does my IF date comparison always return TRUE?

Usually the date is typed in quotes, so Excel compares a number with text, and text is always the larger one. Replace "10/15/2026" with DATE(2026,10,15). It can also mean one of the columns holds text dates.

Q: How do I check if a date is between two dates?

Use AND: =IF(AND(A2>=DATE(2026,10,1),A2<=DATE(2026,10,31)),"October","Other"). Both tests must be TRUE for the date to count as in range.

Quick answerCompare the two date cells directly inside IF: =IF(D2<=C2,"On time","Late") returns "On time" when the date in D2 is on or before the date in C2. For a fixed date, use DATE: =IF(C2<DATE(2026,10,15),"Before","On or after"). Add a blank check first, because an empty cell counts as the earliest possible date.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	F
1	Order	Customer	Due date	Delivered	Cutoff
2	SO-3101	Fenwick Hardware	Oct 5, 2026	Oct 3, 2026	Oct 15, 2026
3	SO-3102	Calder Clinic	Oct 8, 2026	Oct 8, 2026
4	SO-3103	Brook Street Deli	Oct 12, 2026	Oct 15, 2026
5	SO-3104	Hollis & Grant	Oct 20, 2026
6	SO-3105	Marlow Books	Oct 22, 2026	Oct 21, 2026
7	SO-3106	Tidewater Gym	Oct 27, 2026	Nov 2, 2026
8	SO-3107	Juniper Florist	Oct 30, 2026
9	SO-3108	Kestrel Cycles	Nov 4, 2026	Oct 31, 2026

Download this sheet as an Excel practice file (.xlsx)

Compare two date cells with IF

Excel stores every date as a whole number, counting days from 1 January 1900, and a later date is a bigger number. So the ordinary comparison operators work on dates: < means before, > means after, <= means on or before.

Column C holds each order's due date and column D the day it arrived. Fill the formula down and each row compares its own two cells.

=IF(D2<=C2,"On time","Late")

Result: On time

On time: SO-3101 arrived on 3 October, two days before its 5 October due date.

"Before" or "on or before"? Watch the equal date

SO-3102 arrived on its due date. With <= it is on time; with < it is late, because the two dates are equal and neither is before the other. Decide which rule your business uses and write it down next to the column, because this one character changes the result on every order that lands on the day.

The same applies to after: > means strictly after, >= includes the same day.

=IF(D3<C3,"On time","Late")

Result: Late

Late, even though SO-3102 arrived exactly on 8 October. Change < to <= and it becomes On time.

Blank cells count as the earliest date

An empty cell is treated as 0 in a comparison, and 0 is before every real date. So an order that has not arrived yet passes the "on or before" test. =IF(D5<=C5,"On time","Late") says On time for SO-3104, which has no delivery date at all.

Test for the blank first. The outer IF catches the empty cell, and only rows with a date reach the comparison. Use the same pattern for "after" tests: a blank is never after a real date, so it would quietly come out as FALSE.

=IF(D5="","Not delivered",IF(D5<=C5,"On time","Late"))

Result: Not delivered

Not delivered for SO-3104, instead of a false On time.

Compare with a fixed date: use DATE(), not "10/15/2026"

It is tempting to type the date in quotes, =IF(C2<"10/15/2026",...). Do not. In quotes it is text, and when Excel compares a number with text it always treats the text as larger. Every date in the column is "before" the text, so the test returns TRUE for 4 November too.

DATE(year, month, day) builds a real date that works the same on every computer, whatever its regional date order. Better still, put the cutoff in a cell (F2 here) and refer to it as $F$2, so you can change it without editing formulas.

=IF(C2<DATE(2026,10,15),"Before","On or after")

Result: Before

Before: SO-3101 was due on 5 October, ahead of the 15 October cutoff. =IF(C2<$F$2,...) gives the same result.

Show how many days late

Subtracting one date from another gives the number of days between them. Wrap it in a test so early orders show 0 instead of a negative number. Format the result cell as General or Number; if Excel formats it as a date, you will see something like 3 January 1900.

To combine the label and the number, nest it: =IF(D7="","Not delivered",IF(D7<=C7,"On time","Late by "&D7-C7&" days")) returns "Late by 6 days" for SO-3106.

=IF(D4>C4,D4-C4,0)

Result: 3

3: SO-3103 was due on 12 October and arrived on the 15th.

Count dates before a date (COUNTIF treats blanks differently)

To count rather than flag, join the operator to the date with &. Unlike a plain comparison, COUNTIF and COUNTIFS skip empty cells when the criteria is a number or date, so undelivered orders are not counted as early here.

When a date column also holds times (for example 14 October 2026 3:30 PM from a time-clock export), the time makes the value slightly bigger than the date alone, so it fails an "on or before 14 October" test. Compare INT of the cell to strip the time, or use < the next day.

=COUNTIF(D2:D9,"<"&$F$2)

Result: 2

2 deliveries arrived before 15 October (3 and 8 October). The two blank cells in column D are not counted.

Common mistakes

Typing the date in quotes inside IF

"1/1/2026" in a comparison is text, and numbers are always less than text, so the test is TRUE for every date. Use DATE(2026,1,1) or a cell reference.

Empty cells passing as "before"

A blank counts as 0, the earliest date. Wrap the test: =IF(D2="","",IF(D2<=C2,"On time","Late")).

Dates stored as text

Imported dates that are text compare as text, so every comparison with a real date goes wrong. Check with =ISTEXT(D2) and convert the column before comparing.

Using < when the rule is "on or before"

A date equal to the deadline fails <. Use <= for "on or before" and >= for "on or after".

FAQ

How do I check if a date is before another date in Excel?

Compare the cells: =A2<B2 returns TRUE if the date in A2 is earlier. Wrap it in IF for a label: =IF(A2<B2,"Before","Not before").

How do I write an IF formula for a date before today?

Use TODAY(): =IF(A2<TODAY(),"Past","Today or later"). TODAY() recalculates every day, so the result changes over time; add a blank check if the column has empty cells.

Why does my IF date comparison always return TRUE?

Usually the date is typed in quotes, so Excel compares a number with text, and text is always the larger one. Replace "10/15/2026" with DATE(2026,10,15). It can also mean one of the columns holds text dates.

How do I check if a date is between two dates?

Use AND: =IF(AND(A2>=DATE(2026,10,1),A2<=DATE(2026,10,31)),"October","Other"). Both tests must be TRUE for the date to count as in range.

Functions used

Related guides

Updated November 2, 2026