IF Statements for Dates in Excel: Examples and Rules

Formulas & functions · Updated November 20, 2026

To write an IF statement for dates in Excel, compare a cell containing a real date with a date or date-based rule. For example, with a due date in A2, use =IF(A2<TODAY(),"Past due","Not past due") to label dates before today; replace the test or result text to suit your situation.

What an IF statement does with dates

Excel’s IF function checks whether a condition is true and returns one result if it is, or another result if it is false. Its structure is =IF(logical_test,value_if_true,value_if_false). Dates work in the logical test because Excel stores valid dates as numbers, with each date corresponding to a serial value. That lets Excel compare dates using operators such as less than, greater than, and equal to.

The displayed format can make a date look like text, but appearance alone does not tell you how Excel stores it. A genuine date can be shown as 6/15/2026, June 15, 2026, or another date format and still be compared correctly. If a date was imported or entered as text, however, a comparison may not behave as expected. The distinction between a date value and text that looks like a date is behind many date-formula problems.

IF does not change the date or apply date formatting by itself. It returns the value you specify, which might be a label, a number, a date, or a blank string. If the result is a date, format the formula cell as a date using Home > Number Format. If the result is a label such as “Late,” the output is text.

Compare a date with today

A common use is to check whether a deadline has passed. With due dates in A2:A100, enter the following formula beside the first date and fill it down. Each row then compares its own due date with the current date.

With the due date in A2, use: =IF(A2<TODAY(),"Past due","Not past due")

TODAY returns the current date without a time. If A2 contains yesterday’s date, the result is “Past due”; if it contains today or a later date, the result is “Not past due.” The comparison uses a strict less-than sign, so a deadline due today is not counted as overdue. To treat the due date itself as late, use less than or equal instead.

TODAY updates when Excel recalculates the workbook, so the label can change from one day to the next without editing the formula. It is useful for live dashboards, but it may not be appropriate for a report that must preserve the status as of a past date. For a fixed reporting cutoff, enter that cutoff in a cell and compare dates with that cell instead.

Check whether a date is before, after, or equal to a target

Choose the comparison operator to match the rule. Less than means earlier than the target; greater than means later. Adding an equals sign includes the target date. This detail matters for boundaries such as an application deadline, a renewal date, or the first day a policy takes effect.

With a date in A2, use this formula to identify June 15, 2026 exactly: =IF(A2=DATE(2026,6,15),"Target date","Other date")

With a date in A2, use this version to classify dates on or after January 1, 2026: =IF(A2>=DATE(2026,1,1),"On or after cutoff","Before cutoff")

DATE(year,month,day) builds a date from numeric parts, avoiding reliance on how a typed date such as 1/2/2026 is interpreted in different regional settings. Excel can normalize some out-of-range day or month arguments rather than treating them as errors, so use the intended year, month, and day values when constructing a cutoff.

Test whether a date falls within a range

To check whether a date lies between a start and end date, combine two comparisons with AND. AND is true only when both conditions are true. For an inclusive range, use greater than or equal to the start and less than or equal to the end, so dates on either boundary count as inside.

With the date to check in A2, the start date in B2, and the end date in C2, use: =IF(AND(A2>=B2,A2<=C2),"Within range","Outside range")

This setup is useful for checking whether an event falls within a booking period, whether a transaction belongs to a reporting period, or whether a date is covered by a campaign. Before filling the formula down, confirm that each row’s start and end dates are the intended ones. If B2 and C2 should stay fixed while the formula is copied, use absolute references such as $B$2 and $C$2 in the formula; if each row has its own range, keep the references relative.

For a rolling window, compare against TODAY plus or minus a number of days. With a target date in A2, use: =IF(AND(A2>=TODAY(),A2<=TODAY()+30),"Due in the next 30 days","Outside next 30 days")

This checks dates from today through 30 days from today, including both endpoints. Because the formula also requires the date to be today or later, dates already past are not accidentally labeled as upcoming. TODAY()+30 means 30 calendar days, not 30 working days.

Build dates reliably in formulas

When a formula needs a fixed date, DATE is generally clearer than typing a date inside the formula. Date strings can be interpreted differently depending on regional settings, while DATE uses year, month, and day arguments. You can also put a cutoff in a worksheet cell, format that cell as a date, and refer to it in the IF test. That makes a rule easier to update without editing a formula.

For example, if the cutoff date is in D1 and the date being checked is in A2, the formula can refer to D1. With the cutoff in D1 and the checked date in A2, use: =IF(A2<D1,"Before cutoff","On or after cutoff")

Entering a date directly into a cell is often better than embedding it in a long formula when other people may need to change it. Excel usually recognizes dates entered in a familiar local format, but recognition depends on the value and regional settings. If you are unsure whether a cell contains a true date, change its number format temporarily to General. A serial number indicates a numeric value; a string that remains date-shaped may be text and need conversion.

Handle blank cells and times

A blank date cell can produce a misleading outcome in a comparison. Since a truly empty cell may be treated like zero in some numeric comparisons, a blank can be labeled as an old date. Add a blank check before the date test so the formula returns nothing until a date is entered.

With the date in A2, use: =IF(A2="","",IF(A2<TODAY(),"Past due","Not past due"))

The first IF checks whether A2 is blank. If it is, the formula returns an empty string; otherwise, the inner IF checks the date. This is especially useful in a list that has future records or incomplete entries. Note that a cell containing a formula that returns an empty string can also appear blank; the test above treats that result as blank as well.

Times are stored as fractions of a day, so a cell may contain both a date and a time even if its format displays only the date. If you need to include every time on an end date, testing for a value less than or equal to the end date’s midnight can exclude later times that same day. A reliable range pattern is to include the start date and compare against the day after the end date, without including that next day.

With a timestamp in A2, a start date in B2, and an end date in C2, use: =IF(AND(A2>=B2,A2<C2+1),"Included","Excluded")

For example, if C2 is June 15, this test includes timestamps during June 15 and excludes midnight at the start of June 16. It assumes B2 and C2 are dates without time components. If the required rule is based on exact times, enter start and end date-times explicitly and compare against those instead.

Use multiple date conditions or outcomes

AND is useful when every condition must be true, such as a date being after the start and before the end. OR is useful when any one of several conditions is enough. For example, a record might qualify if its date is before one cutoff or after another. Keep the conditions readable and test the boundary dates as well as dates inside and outside the range.

For more than two outcomes, IF functions can be nested: the false result of one IF becomes another IF. This works, but long nested formulas are harder to inspect and easier to mis-parenthesize. In Microsoft 365, IFS can be an alternative for testing several conditions in order; it is also available in Excel 2019 and later. Whichever approach you use, arrange tests so that the most specific or earliest condition is evaluated in the intended order, and include a final default result.

For a simple three-way status, you could distinguish past dates, today, and future dates with nested IF. With a date in A2, use: =IF(A2<TODAY(),"Past",IF(A2=TODAY(),"Today","Future"))

This formula returns “Past” for dates before today, “Today” for the current date, and “Future” for later dates. If A2 contains a time as well as a date, equality with TODAY may fail because TODAY represents midnight. Use a date-only value or adjust the test to account for the time component.

Use conditional formatting instead of a label

If the goal is to draw attention to dates rather than display words in another column, conditional formatting may be a better fit. Select the date cells, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Choose a format such as a fill color and apply the rule. Conditional formatting changes appearance; it does not replace the value or return a text label.

For dates in A2:A100, with A2 as the active cell when the rule is created, use this rule formula: =A2<TODAY()

Apply the rule to the selected range A2:A100. The row reference adjusts for each cell in the range, so each date is checked separately. If the rule is meant to compare every row against one fixed date in D1, use an absolute reference for that cutoff. On Windows, F4 while editing a cell reference cycles through reference types; on Mac, the equivalent shortcut depends on the keyboard and Excel settings, so typing the dollar signs directly is reliable.

Excel for Mac has conditional formatting and the same general date-comparison approach, though ribbon layout and shortcut behavior can differ from Windows. Excel for the web also supports many common conditional-formatting rules, but some advanced options may differ from the desktop app.

Common date formula problems

  • The result is wrong for a date that looks correct: Check whether the source is a real Excel date or text. Re-entering the value in a recognized date format or converting imported text may be necessary.
  • A deadline due today is labeled overdue: Check whether the test uses less than or less than or equal. For a deadline that is not late until after its due date, use a strict less-than test against TODAY.
  • A date range misses the final day: Confirm the end boundary uses less than or equal for date-only values. If the source contains timestamps, consider comparing with the day after the end date as shown above.
  • The formula displays a number: The result may be a date serial number. Apply a date format if the result is meant to be a date; number formatting does not change the underlying value.
  • The formula changes on a later day: TODAY is dynamic and recalculates. Use a fixed date cell for a historical snapshot or a cutoff that should not move.
  • Excel rejects the formula: Check that the parentheses and quotation marks are balanced. In some regional configurations, Excel expects semicolons rather than commas as argument separators; use the separator required by the installation.

When troubleshooting, test the formula on a small set of known cases: one date before the cutoff, the cutoff itself, one date after it, and a blank. These checks expose boundary and missing-value issues quickly. If the workbook uses dates from different systems or has been moved between older workbooks, verify that displayed dates still represent the intended calendar days.

Frequently Asked Questions

How do I write an IF formula for a date before today?
With a real date in A2, use =IF(A2<TODAY(),"Before today","Today or later"). TODAY updates with recalculation, so the classification changes as the calendar advances.

How do I check whether a date is between two dates in Excel?
Use AND to check that it is on or after the start and on or before the end. For a date in A2 and boundaries in B2 and C2, the inclusive test is =IF(AND(A2>=B2,A2<=C2),"Within range","Outside range").

Can an IF statement return a date?
Yes. IF can return a date value from either branch. Format the result cell as a date so Excel displays it as a calendar date rather than a serial number.

Why does my IF formula treat a date as text?
The source cell may contain text that only looks like a date, often because it was imported or entered in a format Excel did not recognize. Check the cell’s value and regional date settings, then convert or re-enter it as a real date before relying on comparisons.

How do I make a date formula ignore blank cells?
Check for a blank first, then run the date comparison only when a date is present. For A2, a nested IF can return an empty string for blank input and a status for nonblank input, as shown in the blank-cells section.

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.