Using IF in Excel for Dates: Rules, Formulas, and Examples

Formulas & functions · Updated November 20, 2026

Yes. In Excel, use IF to test a date against another date, a fixed cutoff, or a date range, then return a label, value, or blank result. For example, with a due date in A2, =IF(A2<TODAY(),"Overdue","Not overdue") checks whether it is before today; use a real date value such as a cell reference or DATE, rather than relying on an ambiguous typed date inside the formula.

How IF works with dates

Excel’s IF function evaluates a condition and returns one result when that condition is true and another when it is false. Its general syntax is =IF(logical_test,value_if_true,value_if_false). The logical test can compare dates using the same operators used for numbers: equal to (=), greater than (>), less than (<), greater than or equal to (>=), or less than or equal to (<=).

Excel stores valid dates as serial numbers, with each day represented by a number. The cell may display a friendly date such as Nov 3, 2026, but a comparison works on the underlying value. This is why date comparisons can use ordinary operators. The cell’s number format controls how a date looks; it does not turn a valid date into a different date.

For example, with due dates in A2:A20, enter this formula beside the first date and fill it down:

With the due date in A2 and today being November 3, 2026, the formula returns Overdue for October 30, 2026, and Not overdue for November 3, 2026 or a later date.

=IF(A2<TODAY(),"Overdue","Not overdue")

TODAY() returns the current date and updates when Excel recalculates the workbook. If a date is due today, the less-than test is false, so this example calls it not overdue. Change < to <= if your rule treats a deadline as overdue on the due date itself.

Use DATE for fixed dates

When the comparison date is fixed, use Excel’s DATE function to build it from a year, month, and day. This avoids confusion between regional date formats: 4/5/2026 can mean April 5 or May 4, depending on settings. DATE(2026,4,5) consistently represents April 5, 2026.

With a date in A2, this example identifies dates on or before December 31, 2026:

=IF(A2<=DATE(2026,12,31),"2026 or earlier","After 2026")

For A2 containing December 31, 2026, the result is 2026 or earlier. For January 1, 2027, it returns After 2026. A cell reference is often more convenient than a fixed date: put the cutoff in B1 and compare A2 with B1. Then updating the cutoff does not require editing the formula.

Avoid typing a date as quoted text in a formula, such as "12/31/2026". Excel may interpret it according to regional settings, or treat it as text rather than a date value. Use a date cell or DATE for dependable comparisons.

Check whether a date falls in a range

To test whether a date falls between two boundaries, combine two comparisons with AND. The boundary operators determine whether the endpoints count. Use greater-than-or-equal and less-than-or-equal for an inclusive range; use strict greater-than or less-than if the start or end date should not qualify.

With the date being tested in A2, this formula checks whether it falls in the first quarter of 2026, including January 1 and March 31:

=IF(AND(A2>=DATE(2026,1,1),A2<=DATE(2026,3,31)),"Q1 2026","Outside Q1")

If A2 contains February 14, 2026, the result is Q1 2026; if it contains April 1, 2026, the result is Outside Q1. AND is appropriate because both conditions must be true. For a range with boundaries stored in B1 and C1, use those cells instead of the two DATE expressions, then copy the formula down as needed.

You can also test month and year separately, which is useful when the dates may fall on any day in a particular month. With a valid date in A2, use:

=IF(AND(YEAR(A2)=2026,MONTH(A2)=2),"February 2026","Other month")

For February 14, 2026, this returns February 2026. Checking both month and year matters: a test of the month alone would also match February in every other year.

Handle blank cells and missing dates

A blank date cell can produce a misleading result in a comparison. Because blank cells may be treated like zero in numeric comparisons, a simple test such as “date is before today” can classify an empty cell as overdue. Add a blank check first when missing dates should remain unlabelled.

With a due date in A2, this formula returns an empty string if A2 is blank; otherwise it checks the deadline:

=IF(A2="","",IF(A2<TODAY(),"Overdue","On time"))

If A2 is empty, the displayed result is blank. If A2 contains October 30, 2026, and today is November 3, 2026, the result is Overdue; a date equal to or after today returns On time. The empty string is not the same as leaving the formula cell empty, but it makes the cell appear blank and keeps the formula available.

If the workbook needs to distinguish an empty cell from a formula that returns an empty string, use a more specific check, such as ISBLANK for a genuinely empty cell. Also decide what a missing date should mean in the business rule: it might mean “not scheduled,” “needs attention,” or simply no label. Excel cannot infer that policy.

Account for times as well as dates

Excel can store a time as the fractional part of a date serial number. A value displayed as November 3, 2026 may therefore include a hidden time, such as 3:00 p.m. A test against DATE(2026,11,3) compares with midnight at the start of that day. As a result, a timestamp later that day is greater than the date value, even if both cells display the same calendar date.

If you are checking whether a timestamp falls on one calendar day, use a start-inclusive, next-day-exclusive range. With a timestamp in A2, this identifies any time on November 3, 2026:

=IF(AND(A2>=DATE(2026,11,3),A2<DATE(2026,11,4)),"That day","Another day")

A timestamp of November 3 at 3:00 p.m. returns That day, while exactly midnight on November 4 returns Another day. This pattern is also useful for daily reporting because it includes every time on the selected date without accidentally including the next day’s midnight.

For deadline logic that must consider the current time, TODAY() may be too broad because it returns a date without a time. NOW() returns the current date and time. Choose between them based on the rule: a deadline measured by calendar day normally uses TODAY(), while a deadline that expires at a particular hour may need a comparison with a timestamp and NOW().

Common date formula problems

  • A date is stored as text. Imported data or text pasted from another system may look like a date without being a numeric date value. A comparison can then give an unexpected result. Check whether changing the cell’s number format changes its display; if it does not, the value may be text. Convert or re-import it as a real date before relying on comparisons.
  • The date order is ambiguous. A typed value such as 03/04/2026 depends on regional settings. Use a date cell, a clearly understood entry format, or DATE(year,month,day) in formulas.
  • The comparison boundary is wrong. < excludes a date equal to the cutoff, while <= includes it. Check whether the policy says “before,” “by,” or “through” the date and choose the operator accordingly.
  • Hidden times affect equality. A timestamp at noon is not equal to midnight on the same date. Compare a range from the date’s midnight up to, but not including, the next day when the whole calendar day should count.
  • The displayed date format is mistaken for the stored value. Formatting a number as a date changes how it appears, not what value is stored. Conversely, applying a date format to text does not necessarily convert that text into a valid date.

Workbooks can also use different date systems. Excel for Windows and Mac generally handle dates in the same way within a workbook, but the workbook’s date system can affect raw serial values when data moves between workbooks configured differently. If imported or copied dates shift by a consistent number of days, check the workbook date-system setting and the source data rather than trying to fix the comparison with a different IF operator.

IF versus conditional formatting

Use an IF formula when the result needs to be text, a calculated value, or an input to later formulas. For example, a status column containing “Overdue” can be filtered, counted, or used in another calculation. The formula also makes the rule visible in the worksheet.

Use conditional formatting when the goal is only to change a cell’s appearance, such as highlighting dates that have passed. In Excel for Microsoft 365 on Windows, select the date cells and choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring, then choose an available date period and a format. For custom rules, choose Home > Conditional Formatting > New Rule. The dialog and ribbon arrangement can differ somewhat in Excel for Mac and Excel for the web, but conditional formatting is available in those versions too.

Formatting alone does not create a status value. If users need to sort or filter by a word such as “Overdue,” use IF; if they only need a visual cue, conditional formatting may keep the sheet simpler. Both methods can be used together when a visible status and a color cue are useful.

Frequently Asked Questions

Can IF compare dates directly in Excel?
Yes. Compare a date cell with another date cell, a date created with DATE, or a date returned by a function such as TODAY(). The values must be real Excel dates for reliable results.

How do I check whether a date is overdue?
For a due date in A2 and a rule that considers only dates before today overdue, use =IF(A2<TODAY(),"Overdue","Not overdue"). Add a blank-cell check if empty due dates should not be treated as overdue, and use <= if the due date itself counts as overdue.

How do I compare a date with a specific day?
Use DATE(year,month,day) in the comparison, or put the target date in another cell and refer to it. This avoids relying on a text date whose interpretation may vary with regional settings.

Why does my date IF formula give the wrong result?
Common causes include a date stored as text, an unintended time attached to a date, an incorrect inclusive or exclusive comparison operator, or a blank cell being treated like zero. Check the underlying values and the precise boundary rule.

Can an IF formula check whether a date is between two dates?
Yes. Use AND to require that the date is at least the start date and no later than the end date. Use >= and <= for an inclusive range, or adjust the operators when an endpoint should be excluded.

Does TODAY update automatically?
TODAY() updates when Excel recalculates the workbook; it is not a permanently fixed date. If a report must preserve the date it was prepared, enter or store that date as a fixed value instead of using a continuously updating formula.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.