IF Then for Dates in Excel: Formulas and Examples

Formulas & functions · Updated November 20, 2026

To use IF with dates in Excel, compare a cell containing a real date with another date or a date created by DATE, then return one value if the condition is true and another if it is false. For example, if A2 contains a date, the formula =IF(A2>DATE(2025,1,1),"After cutoff","On or before") labels dates after January 1, 2025, and labels January 1 itself as “On or before.”

How IF Works with Dates

Excel’s IF function evaluates a logical test and returns one result when that test is true and a different result when it is false. Dates work in the test just like numbers because Excel stores valid dates as serial values. That means comparisons such as greater than, less than, or equal to can determine whether one date falls before, after, or on another date.

For the opening example, the assumed layout is a date in A2, with January 1, 2025 as the cutoff. The formula is =IF(A2>DATE(2025,1,1),"After cutoff","On or before"). If A2 contains January 2, 2025, it returns “After cutoff.” If A2 contains January 1, 2025, it returns “On or before,” because the test uses > rather than >=.

The three arguments are the condition, the result for a true condition, and the result for a false condition. Text results need straight double quotation marks. A result can also be a number, another cell reference, or a further calculation. The formula does not change the date in A2; it only evaluates it and displays a result.

Use DATE to Set a Reliable Cutoff

For a fixed cutoff, DATE is usually clearer and safer than typing a date directly into the formula. It takes year, month, and day as separate numbers, so the formula does not depend on whether a computer interprets a typed date as month/day/year or day/month/year. DATE also makes it easy to see exactly which date the condition uses.

Assume A2 contains an order date and the task is to mark orders placed on or after January 1, 2025. Use =IF(A2>=DATE(2025,1,1),"2025 or later","Before 2025"). An A2 value of January 1, 2025 returns “2025 or later”; December 31, 2024 returns “Before 2025.” The equals sign matters because it makes the cutoff date itself qualify.

To enter the formula, select the result cell, type an equals sign followed by the formula, and press Enter. To apply it to additional rows, drag the fill handle at the lower-right corner of the cell down the column. A2 will adjust to A3, A4, and so on. In Microsoft 365 for Windows, date display options are on Home > Number Format; changing the display format affects how a date looks, not the underlying date value.

Check Whether a Date Falls Within a Range

To test whether a date falls between two boundaries, combine the comparisons with AND inside IF. AND returns true only when every condition is true. This is useful for reporting periods, eligibility windows, school terms, and other date ranges.

Assume A2 contains the date to check and the desired period is calendar year 2025. Use =IF(AND(A2>=DATE(2025,1,1),A2<DATE(2026,1,1)),"In 2025","Outside 2025"). January 1 and December 31, 2025 both return “In 2025”; January 1, 2026 returns “Outside 2025.” The upper test is less than the first day of the next year, which makes the year boundary explicit.

If the start and end dates are in cells instead, the formula can refer to those cells. Assume A2 is the date being tested, B2 is the first date in the range, and C2 is the last date, with both endpoints meant to count. Use =IF(AND(A2>=B2,A2<=C2),"Within range","Outside range"). This approach is convenient when dates change regularly because you can update B2 or C2 without editing the formula.

Compare a Date with Today

TODAY returns the current date, so it can make a deadline or status formula update as time passes. It updates when Excel recalculates the workbook; it is not a permanent timestamp. If a file needs to record the date an event occurred, enter or store that date rather than relying on TODAY to preserve it.

Assume A2 contains a due date and the result should say “Overdue” only when that date is earlier than today. Use =IF(A2<TODAY(),"Overdue","Not overdue"). A date before today returns “Overdue”; today and future dates return “Not overdue.” If today’s date should also count as overdue, change the comparison to <=.

A blank due-date cell needs separate attention: Excel treats a blank in many date comparisons like zero, which can make it appear to be an old date. Assume A2 contains a due date that may be blank. Use =IF(A2="","No due date",IF(A2<TODAY(),"Overdue","Not overdue")). This first checks for an empty cell, then evaluates the date only when a value is present. The nested IF is useful here because it gives blanks their own meaningful label.

Choose the Right Comparison and Boundary

Small changes to the comparison operator can change which dates qualify. Decide whether the boundary date counts before writing the formula. For example, “before the deadline” often means strictly earlier than the deadline, while “due on or before” includes the deadline itself.

  • > means later than the comparison date, excluding an exact match.
  • >= means later than or equal to the comparison date.
  • < means earlier than the comparison date, excluding an exact match.
  • <= means earlier than or equal to the comparison date.
  • = checks whether the dates are equal; the cells must represent the same date value for a match.

For instance, assume A2 contains a submission date and submissions received by and including March 31, 2025 are on time. Use =IF(A2<=DATE(2025,3,31),"On time","Late"). March 31 returns “On time,” while April 1 returns “Late.” If the wording instead says “before March 31,” use < so March 31 does not qualify.

Dates with Times and Hidden Values

A cell displayed as a date can also contain a time. Excel stores a date and time as a number with a fractional part for the time, even if the cell format hides it. This can create a surprising result: a value displayed as March 31 at 2:00 p.m. is later than March 31 at midnight.

Assume A2 contains a date-time, while B2 and C2 contain the first and last calendar dates of an inclusive period. To include every time on the final date, use =IF(AND(A2>=B2,A2<C2+1),"Within range","Outside range"). The less-than test against the day after C2 includes times during C2 but excludes midnight at the start of the following day. This is often more dependable than comparing a date-time value to an end date that represents midnight.

If time is not relevant, you can also remove time values in a separate data-cleaning step or ensure the source column contains dates only. Avoid assuming that a date’s displayed format proves there is no time component: the number format can conceal it. Check a suspicious value by changing the cell format to include a time or by examining the source data.

Make Sure Excel Recognizes the Values as Dates

A date-looking entry may actually be text. If Excel has stored “3/4/2025” as text, a date comparison may behave unexpectedly or fail to treat it as a date. Text dates commonly arise from imported CSV files, copied data, or differences in regional date conventions. Formatting text as a date does not always convert it into a real date value.

Check a suspect cell by changing its number format to General. A real date usually appears as a serial number; text remains text. Another clue is that a genuine date often aligns right by default, while text often aligns left, though alignment can be changed manually and is not conclusive. If an imported column is consistently structured, select it and use Data > Text to Columns to review and convert the data, choosing the appropriate date order when prompted. Make a copy first if the source data is important.

For a text date that Excel can interpret according to the workbook’s locale, DATEVALUE can convert it. Assume A2 contains a recognizable date stored as text, such as “1/15/2025.” Use =IF(DATEVALUE(A2)>DATE(2025,1,1),"After cutoff","On or before"). DATEVALUE returns a date serial that IF can compare. However, ambiguous text such as “1/2/2025” can mean different dates in different regional settings, so confirm the source’s date order rather than trusting a conversion that merely completes.

Common Mistakes and Practical Fixes

Several issues recur when people build date-based IF formulas. Checking these details often resolves a result that looks wrong without requiring a more complicated function.

  • The cutoff is typed ambiguously: use DATE(year,month,day) for a fixed date, or reference a cell containing a confirmed real date.
  • The boundary operator is wrong: decide whether the exact cutoff counts, then choose > versus >= or < versus <= accordingly.
  • The input is text: convert or repair the source data before relying on date comparisons, and verify the date order of imported values.
  • Blank cells are labeled overdue: test for blanks before comparing the date with TODAY.
  • A hidden time changes the result: compare against the next day for an inclusive end date, or inspect whether the cell contains a time fraction.
  • The formula appears as text: confirm the cell is not formatted as Text, then change it to General if needed and re-enter the formula.

Also distinguish between a date value and its display. A cell may show “Jan 2025” while containing January 1, 2025, or it may show a short date while containing a time as well. Number formatting changes appearance, whereas IF evaluates the underlying value. When results feed a report or another formula, verify both the data and the intended boundary logic.

Frequently Asked Questions

Can I use IF to check whether a date is before today?
Yes. If A2 contains a real date, use =IF(A2<TODAY(),"Past","Today or later"). TODAY updates with recalculation, so the result can change on a later day.

How do I check whether a date is between two dates?
Use AND inside IF to require both conditions: the date must be on or after the start and on or before the end. Make sure the operators match whether you want the boundary dates included. If the values contain times, use the next day as an exclusive upper limit to include all times on the final day.

Why does my IF formula treat a date as text?
The cell may contain imported text rather than an Excel date serial. Changing the number format alone may not convert it. Use a suitable conversion method, confirm the regional date order, and check the converted value before using it in comparisons.

Can I put a date directly inside an IF formula?
For a fixed date, DATE(year,month,day) is a reliable way to express it, such as DATE(2025,1,1). It avoids ambiguity from typing a slash-separated date whose interpretation can vary by locale.

Does changing the date format change the IF result?
No. A date format changes how a stored value is displayed, not the underlying date serial that IF compares. The result changes only if the underlying value, comparison, or formula changes.

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.