Use Excel’s IF function to test a date and return one result when the condition is true and another when it is false: =IF(B2>DATE(2026,1,1),"After cutoff","On or before cutoff"). Excel does not use a separate THEN keyword; the second and third arguments of IF supply the “then” and “otherwise” results.
How the IF formula works with dates
The formula structure is =IF(logical_test, value_if_true, value_if_false). The logical test compares a date with another date or a date-related value. Excel returns the second argument if the test is true, and the third argument if it is false. You can return words, numbers, another date, or a blank result.
For example, with a date in B2, the formula below checks whether it falls after January 1, 2026. If B2 contains January 15, 2026, it returns “After cutoff.” If B2 contains January 1 or an earlier date, it returns “On or before cutoff.”
With the date to test in B2, enter:
=IF(B2>DATE(2026,1,1),"After cutoff","On or before cutoff")
The comparison operators you can use include > (greater than), >= (greater than or equal to), < (less than), <= (less than or equal to), = (equal to), and <> (not equal to). For dates, “greater than” means later in time, while “less than” means earlier. Choose the operator carefully: using < instead of <= changes whether a date exactly on the cutoff passes the test.
Flag overdue or upcoming dates
A common use is marking a deadline as overdue if it is earlier than today. TODAY() returns the current date according to the computer’s date, and the result updates when Excel recalculates the workbook. With due dates in B2, use this formula in C2:
=IF(B2<TODAY(),"Overdue","Not overdue")
If B2 is October 20, 2026, the result on November 3, 2026 is “Overdue.” A due date equal to today returns “Not overdue,” because the formula uses a strict less-than comparison. If a deadline should count as due today, use less than or equal to instead:
With the due date in B2, enter:
=IF(B2<=TODAY(),"Due","Not due")
This returns “Due” when B2 is today or any earlier date. The distinction is useful for task lists: one definition labels only missed deadlines as overdue, while the other labels a deadline as due as soon as its date arrives. If you need a warning before a deadline, compare the date with a future date calculated from today; explain the lead time in the formula or beside the results so users know what the warning means.
Check whether a date is inside a range
To test whether a date falls between a start and end date, combine two comparisons with AND. It returns TRUE only when both conditions are true. The example below includes both endpoints, so a date equal to the start or end is considered in range.
With the start date in B2, end date in C2, and date being checked in D2, enter:
=IF(AND(D2>=B2,D2<=C2),"In range","Outside range")
If B2 is January 1, 2026, C2 is December 31, 2026, and D2 is June 15, 2026, the formula returns “In range.” If D2 is December 31, 2025, it returns “Outside range.” Make sure the start date is not later than the end date; if the two are reversed, no date can satisfy both comparisons.
You can also compare against fixed calendar boundaries using DATE(year,month,day). This avoids relying on how a computer interprets a typed date such as 1/2/2026, which can mean different dates under different regional settings. For example, with a date in B2, the following formula labels dates in 2026:
=IF(AND(B2>=DATE(2026,1,1),B2<=DATE(2026,12,31)),"In 2026","Outside 2026")
For B2 equal to June 15, 2026, the result is “In 2026.” The DATE arguments are year, month, and day, in that order.
Return a different result based on month or year
Sometimes the test is about the calendar month or year rather than whether a date is before a cutoff. Excel’s MONTH function returns a number from 1 to 12, and YEAR returns the year number. These let an IF formula identify dates in a particular month or year without typing a text date into the comparison.
With a valid date in B2, use:
=IF(MONTH(B2)=1,"January","Other month")
If B2 contains January 15, 2026, the result is “January”; for a date in any other month, it is “Other month.” To check a year, with the date in B2, use:
=IF(YEAR(B2)=2026,"2026","Other year")
For a date in 2026, this returns “2026.” These tests can help categorize records, but they do not check the day within the month. For instance, the month formula treats January 1 and January 31 alike. If the business rule is based on a specific cutoff day, compare the full date or build the cutoff with DATE.
Handle blank date cells
A blank cell can cause misleading results if a formula compares it directly with today. Excel treats a truly empty cell like a zero in many numeric comparisons, and date serial zero is earlier than current dates. As a result, a simple overdue formula can label an empty due-date cell as overdue. Test for a blank first, then perform the date comparison.
With due dates in B2, enter this formula in C2:
=IF(B2="","",IF(B2<TODAY(),"Overdue","Not overdue"))
If B2 is empty, the formula displays an empty string rather than a status. If B2 contains October 20, 2026, then on November 3, 2026 it displays “Overdue.” The empty-looking result is not a genuinely empty cell: it contains a formula returning an empty string. That matters if another formula counts cells or checks whether a cell is blank. If you need to distinguish between a truly empty cell and a formula-generated empty string, account for that difference in later calculations.
Dates, serial numbers, and cell formatting
Excel stores recognized dates as numbers, often called date serial numbers, and displays those values in a date format. A date comparison works on the underlying values, not on how the cells look. Formatting a cell to show “January 2026” does not turn the stored date into text or remove its day value. A cell containing January 31 is still later than one containing January 1, even if both are formatted to display only the month and year.
This explains a frequent surprise: two cells may look like the same date but compare differently if one includes a time. Excel stores times as fractions of a day. A cell displaying January 1 may contain January 1 at 3:00 p.m., which is later than January 1 at midnight. If a condition must include every time on the cutoff date, test for dates earlier than the following day rather than using “less than or equal to” the displayed date. Alternatively, remove time values in the source data when the time is not meaningful.
Check that the source value is a real Excel date, not text that merely looks like one. Text dates may fail comparisons or behave inconsistently because interpretation depends on regional date conventions. In Microsoft 365 for Windows, select the cell and inspect the formula bar and number format; changing the display format alone does not convert text into a date. If you import data, use the import or conversion options that correctly recognize the source’s date order, such as day-month-year versus month-day-year.
Excel workbooks can also use different date systems. This is uncommon in ordinary work, but copying dates between workbooks using different systems can shift displayed dates. If values look wrong after transferring data between platforms or files, check the workbook’s date system before assuming the IF formula is at fault.
Common errors and practical fixes
When a date IF formula gives an unexpected answer, check the input values and the exact condition before rewriting the whole formula. These are common causes:
- Wrong boundary operator: Decide whether a date exactly on the cutoff should pass. Use
>or<to exclude equality, and>=or<=to include it. - Text instead of a date: A date-looking string may not be a numeric date value. Convert or re-import it using the correct regional order, then verify the cell behaves as a date.
- Time included: A hidden time can make two values on the same displayed day unequal. Show a time format temporarily to inspect the value.
- Blank cells: Add a blank check before comparing with
TODAY()if empty records should not receive a status. - Wrong cell references: When copying a formula down, confirm that each row uses its own date cell. Use dollar signs for a fixed reference if a cutoff date is stored in one cell that should not move as you fill the formula.
- Regional formula separators: The examples use commas as argument separators. Excel settings in some regions use semicolons instead; if Excel rejects a formula, use the separator required by that installation.
For a long list, enter the formula in the first result row and fill it down, then test a date before the cutoff, on the cutoff, and after it. This small boundary check is more reliable than judging the result from a single ordinary date. In Microsoft 365 for Windows, you can drag the fill handle at the lower-right corner of the selected cell; double-clicking it can fill alongside adjacent data. On Mac, the same fill handle is available, though trackpad and mouse gestures may differ.
IF formulas versus date formatting
An IF formula is appropriate when you need a result in a cell: a status such as “Overdue,” a number for a report, or a blank when no date is entered. It is not the only way to make dates easier to scan. Conditional formatting changes the appearance of cells that meet a rule but does not replace their values with labels.
In Excel for Microsoft 365 on Windows, select the date cells and choose Home > Conditional Formatting > Highlight Cells Rules to apply a built-in date rule where available, or use Home > Conditional Formatting > New Rule to define a rule. On Mac, the Conditional Formatting controls are also on the Home tab, though the dialog layout can differ. Use a formula when you need a reusable text or numeric answer; use conditional formatting when the underlying date should remain intact and color or another format is enough. The two approaches can also be combined: a formula can create a status column while conditional formatting highlights overdue rows.
Frequently Asked Questions
Does Excel have an IF THEN function for dates?
Excel uses the regular IF function, not a separate IF THEN function. Put the date test first, the result for a true test second, and the result for a false test third.
How do I check whether a date is before another date?
Compare the date cell with another date using <. For example, with the date in B2 and a fixed cutoff of January 1, 2026, the test is B2<DATE(2026,1,1). Put that test inside IF if you want it to return a label.
How do I make Excel say overdue when a date has passed?
With a due date in B2, test whether it is earlier than TODAY(), and return “Overdue” when true. Add a blank check first if missing due dates should not be treated as overdue.
Why does my IF formula show the wrong date result?
Check whether the cell contains a true Excel date or text, whether it includes a hidden time, and whether your boundary operator includes the cutoff day. Also verify that empty cells are handled intentionally.
Can an IF formula return a date?
Yes. Its true and false results can be date values or references to cells containing dates. Format the result cell as a date if Excel displays the underlying serial number instead of a calendar date.
Related
- How to Space Down in Excel Using Indent
- How to Sort Google Sheets: Rows, Columns, and Filters
- How to Shuffle Sheets in Excel With a Keyboard Shortcut
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LAMBDA Examples for Reusable Custom FunctionsExcel’s LAMBDA function lets you turn a formula into a reusable custom function. For example, a definition with…
- Excel SUM and XLOOKUP: How to Add Lookup Results in ExcelTo add the values returned by XLOOKUP, wrap the lookup formula in SUM: with prices in B2:B5, matching…
- Excel Yield Analysis: Formulas for Bonds and ProductionExcel yield analysis depends on what you mean by yield: for a bond’s yield to maturity, use Excel’s…
- Excel Protect Sheet: Allow Users to Filter and SortYes. In Excel for Microsoft 365 on Windows, turn on the filter before protecting the worksheet, then choose…
- Excel UNIQUE Function Not Showing? Causes and FixesIf Excel does not recognize or display UNIQUE, first check your version: the function is available in Microsoft…
- Calculating PMT in Excel: Formula, Payments, and ExamplesTo calculate a fixed periodic loan payment in Excel, use the PMT function, making sure the interest rate…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.