IF Formulas With Dates in Excel: Before, After, Overdue

An IF date formula in Excel compares a date with another date and returns a result, such as "Overdue" or "On time". It works because Excel stores every date as a number, so a later date is simply a bigger number. This guide shows the patterns you'll use most: overdue checks with TODAY, comparisons with a fixed DATE, due-soon warnings, late deliveries, and the traps that make date formulas silently fail.

How Excel compares dates#

Excel stores each date as a whole number: the count of days since 1 January 1900 (on Windows and current Mac versions). 1 November 2026 is stored as 46,327. What you see is just formatting.

That means the normal comparison operators work on dates:

The examples use this invoice list in A1:C5:

How to compare two dates with IF#

Compare the two date cells directly. To check whether each invoice was paid late, type this in D2 and fill down (Windows: Ctrl+D; Mac: ⌘D):

=IF(C2>B2,"Late","On time")

INV-01 was paid early and INV-03 on the due date, so both show On time. INV-02 was paid four days late and shows Late. Paying on the due date counts as on time here because > doesn't include equal dates.

INV-04 has no paid date yet, and the formula shows On time, which is misleading. The blank-date section below fixes that.

How to flag overdue items with TODAY#

TODAY() returns the current date and updates every time the workbook recalculates. Compare a due date with it to flag anything overdue:

=IF(B2<TODAY(),"Overdue","Not due yet")

If today is 1 November 2026, every due date in the table is in the past, so all four rows show Overdue. That's only useful for unpaid invoices, so combine it with the paid date:

=IF(AND(C2="",B2<TODAY()),"Overdue","")

Now only INV-04 shows Overdue: it has no paid date and its due date has passed. The TODAY function guide explains how TODAY recalculates, and the guide to IF with AND covers combining tests.

How to compare with a fixed date#

Build the date with DATE(year, month, day) inside the formula:

=IF(B2<DATE(2026,10,1),"Q3","Q4")

INV-01 and INV-02 are due in Q3; INV-03 and INV-04 in Q4.

Don't write the date in quotes, like B2<"01/10/2026". Excel treats that as text, and when it compares a number with text, the text always counts as bigger. The test returns TRUE for every date, with no error to warn you. DATE also avoids confusion between day-month and month-day order, because the arguments are always year, month, day.

You can also test parts of a date. =IF(WEEKDAY(B2,2)>5,"Weekend","Weekday") checks whether a date falls on a Saturday or Sunday: with 2 as the second argument, WEEKDAY numbers Monday as 1 and Sunday as 7. Similarly, =IF(MONTH(B2)=12,"December","") picks out one month in any year.

How to flag dates due soon#

Subtract today's date from the due date to get the number of days left, then test that number.

=IF(AND(B2>=TODAY(),B2-TODAY()<=7),"Due soon","")

This shows Due soon for dates from today up to seven days ahead. The first test stops past dates from being flagged, because their days-left number is negative and would also be less than 7.

To show the number of days overdue instead of a label:

=IF(B2<TODAY(),TODAY()-B2,0)

If the result shows as a date, such as 12-Jan-1900, change the cell's format to Number. For a full tracker with colours and warnings, see building an expiry date tracker.

How to handle blank dates#

An empty cell counts as 0 in a comparison, and 0 is the earliest date Excel knows. So =IF(B2<TODAY(),"Overdue","") flags every empty row as overdue. Check for a blank first:

=IF(B2="","",IF(B2<TODAY(),"Overdue",""))

The same fix makes the late-payment formula honest about unpaid invoices:

=IF(C2="","Unpaid",IF(C2>B2,"Late","On time"))

INV-04 now shows Unpaid instead of On time. The guide to IF cell is blank covers other blank checks.

How to check if a date is between two dates#

Use AND with a lower and an upper limit, just like with numbers:

=IF(AND(B2>=DATE(2026,10,1),B2<=DATE(2026,10,31)),"October","")

INV-03 and INV-04 are due in October. The guide to IF between two numbers explains inclusive and exclusive limits. To total or count by date range instead of labelling each row, SUMIFS and COUNTIFS take date criteria, such as ">="&DATE(2026,10,1); the lesson on dates in criteria practises exactly that.

Why your IF date formula isn't working#

Check these when the results look wrong:

1. Dates stored as text. Imported dates often sit on the left of the cell. Convert them with DATEVALUE or Data > Text to Columns.
2. Dates typed in quotes in the formula. Use DATE(2026,10,1) instead.
3. Times hidden in the dates. A value of 10 Oct 2026 3:00 PM is greater than DATE(2026,10,10). Compare with <DATE(2026,10,11) to include the whole day.
4. Blank cells treated as old dates. Add a blank check first.
5. Results shown as dates. Day differences sometimes inherit a date format. Switch the cell to Number.

For more IF basics, see the IF function explained simply or the IF function guide. If the cell holds words rather than dates, see IF with text.

Practise it#

Date logic shows up in operations, finance and admin tests: overdue invoices, expiring contracts, late deliveries. Practise dates in criteria in a live spreadsheet: you type the formula and it checks it instantly. Then practise the IF function, and try the operations case study to use date checks on a realistic task.