With due dates in A2:A20, enter a formula in B2 to label each date as overdue or not: =IF(A2<TODAY(),"Overdue","Not overdue"). Excel compares real date values, then returns the result you specify. Before using the formula, make sure the cells contain dates Excel recognizes, and fill the formula down for the other rows.
What You’ll Need
- A Microsoft Excel workbook in Microsoft 365 for Windows. The examples also work in current desktop Excel for Mac and Excel for the web.
- A column containing dates, such as due dates in A2:A20, and an empty column for the results.
- A clear rule: for example, whether a date is before today, on a particular day, or within a date range.
Excel stores recognized dates as numbers, even when they appear on screen in a format such as 6/30/2026. An IF formula can compare those date values using operators such as less than (<), greater than (>), or equal to (=). The display format does not change the underlying comparison.
Step-by-Step
- Check the date column. Select a few cells in your date column and look at the formula bar. A recognized date is usually shown as a date there, and you can change its appearance using Home > Number group > Number Format. If a date is left-aligned or has a warning indicator, it may be stored as text. Convert text dates before relying on comparisons; changing the cell’s number format alone does not convert text into dates.
- Decide what the IF formula should test. Write the rule in everyday language first: “If the due date is before today, show Overdue; otherwise, show Not overdue.” Decide what should happen when the test is false, too. IF requires a condition, a result when that condition is true, and a result when it is false.
- Choose a cell for the result. If the dates start in A2, click B2. Type the formula and press Enter. For due dates in A2:A20, the formula returns “Overdue” when A2 is earlier than today, and “Not overdue” when A2 is today or later.
=IF(A2<TODAY(),"Overdue","Not overdue")The TODAY function supplies the current date and updates when Excel recalculates, so a result can change from one day to the next. - Fill the formula down the column. Select B2 and drag the fill handle—the small square at the cell’s lower-right corner—down through the rows you need. You can also copy B2, select the destination cells, and paste. Excel adjusts A2 to A3, A4, and so on, so each result checks the date on the same row. Confirm that the final row points to the matching date cell.
- Use a fixed date when the rule should not move. For instance, if A2 contains a date and the test is whether it falls before January 1, 2026, compare it with DATE rather than typing an ambiguous date string. With the date to check in A2, this formula returns “Before” for December 31, 2025, and “On or after” for January 1, 2026, or any later date.
=IF(A2<DATE(2026,1,1),"Before","On or after")DATE takes the year, month, and day as separate numbers, so the formula is not dependent on whether the computer displays dates as month/day/year or day/month/year. - Test for an exact date. If A2 is meant to match June 30, 2026, use a DATE expression for the comparison. With the date to check in A2, the formula returns “Due today” only when the date value is June 30, 2026; otherwise it returns “Other date.”
=IF(A2=DATE(2026,6,30),"Due today","Other date")The phrase “Due today” here is a label chosen for the example; this formula checks a fixed calendar date, not the actual current day. - Check whether a date falls within a range. Put the earliest and latest allowed dates in C1 and D1, and the date to test in A2. With both endpoints included, the formula returns “In range” when A2 is on or after C1 and on or before D1. It returns “Outside range” for dates before or after those endpoints.
=IF(AND(A2>=C1,A2<=D1),"In range","Outside range")AND makes both comparisons necessary. Enter actual dates in C1 and D1, and consider keeping those boundary dates visible so the rule is easy to review. - Decide how blanks should behave. A blank date cell can be treated like zero in a comparison, which may incorrectly label an empty row as overdue. If A2 may be blank, check for blank first. With due dates in A2:A20, this formula returns an empty result for a blank A2; otherwise it returns the appropriate overdue label.
=IF(A2="","",IF(A2<TODAY(),"Overdue","Not overdue"))The empty result is a zero-length text value, so the cell looks blank even though it contains a formula. - Check the result with known cases. Try one date before the cutoff, one date on it, one date after it, and one blank if blanks are possible. For a less-than comparison, a date exactly equal to the cutoff is false; for a less-than-or-equal comparison, it is true. Testing these boundary cases helps confirm that the wording and operators match the rule you intended.
Common Mistakes
- Typing dates as text. A value that looks like a date may have been imported as text. Excel may then compare it unpredictably or fail to treat it as a date. Check the value and convert it to a real date before using it in the IF test. Text to Columns or a carefully chosen conversion method may help, but verify the result rather than assuming formatting fixed it.
- Putting the date in quotation marks. A quoted date such as
"6/30/2026"is text, and its interpretation can depend on regional settings. Use DATE(year,month,day) for a fixed date, or refer to a cell that contains a valid date. - Using the wrong boundary operator. The formula with < excludes a date equal to the cutoff. Use <= if the cutoff day should count as earlier or due. Similarly, an inclusive range needs >= for its start and <= for its end.
- Forgetting blanks. An empty cell can behave like an early date in comparisons. Add a blank check first if empty rows should stay empty, rather than showing an overdue or outside-range message.
- Expecting TODAY to stay fixed. TODAY changes as Excel recalculates. Use it for rules that should be relative to the current day. For a historical report that must keep the same cutoff, use a date stored in a cell or a fixed DATE value instead.
- Breaking references while copying. Relative references change as you fill a formula down. This is right for row-by-row checks, but a cutoff stored in C1 should usually remain fixed. Use
$C$1to lock that reference before copying if needed; you can press F4 while editing a reference in Windows Excel to cycle through reference types.
Tips
- Keep the formula readable by using short, consistent result labels such as “Overdue,” “Due,” and “Upcoming.” Avoid labels that imply more than the comparison actually checks.
- For formulas with several possible date conditions, map out the order of the tests before nesting IF functions. Excel returns the result for the first condition that is true, so a broad condition placed first can prevent a more specific one from being reached.
- Use cell references for cutoff dates that may change. That lets you update one date in a worksheet instead of editing formulas throughout a column.
- Format the source and cutoff cells as dates for people reading the worksheet. Formatting makes values easier to inspect, but remember that a number format does not repair text stored as a date.
- If a date test is used for reporting, label the cutoff and explain whether the endpoint is included. For example, “On or before the deadline” is clearer than “Valid” when users need to understand the rule.
- On Mac and Excel for the web, the formula syntax and examples here are the same. Ribbon placement can vary slightly by platform or window size; in desktop Excel for Mac, use the Home tab’s number-format controls to display dates. The Windows F4 reference shortcut differs on some Mac keyboards, so you can type dollar signs into the reference directly instead.
Troubleshooting
The formula shows the formula text instead of a result. The cell may be formatted as Text, or Show Formulas may be on. Change the cell format to General, press F2 and then Enter to re-enter the formula, and check Formulas > Show Formulas. If you are using Excel for Mac, the corresponding ribbon command is also on the Formulas tab, though the ribbon layout can differ.
A date that should be overdue is not flagged. Check that the cell contains a real date, not text, and confirm the comparison uses the intended direction. A date later than today will not satisfy a less-than test. Also check whether the source includes a time: a value representing today at midnight is not earlier than TODAY(), but a timestamp later today is greater than TODAY(). Decide whether your rule concerns calendar days or exact times.
A result changes unexpectedly. If the formula uses TODAY(), the current date is recalculated, so a deadline can move from “Not overdue” to “Overdue” after the day changes. If the result should use a fixed reporting date, store that date in a worksheet cell and compare against that reference instead. Verify calculation settings if formulas seem not to update; under Formulas > Calculation Options, Automatic is the usual setting.
The formula returns #VALUE! or another error. Inspect every referenced cell, especially the cutoff cells, for text, errors, or unexpected spaces. Check that parentheses and commas are in place. These examples use commas between function arguments; Excel installations configured for some regional settings may display or require semicolons instead. Follow the separator used by your Excel installation if it rejects a formula copied from another locale.
Every row shows the same result. Look at the formula bar in several result cells. If each formula still refers to A2, the reference may have been locked as $A$2 or the formula may have been copied as text. For a row-by-row check, use a relative reference such as A2 and fill down. Lock only references that should remain constant, such as a shared cutoff date.
Frequently Asked Questions
Can an IF formula compare dates directly?
Yes. Excel compares valid date values with the same operators used for numbers: <, >, =, <=, and >=. The two values being compared must be dates Excel recognizes, or the result may not match the intended chronological order.
How do I check whether a date is today?
Use TODAY() as the comparison value, with the date to check in A2. For example, with a date in A2, this returns “Today” only if that date equals the current date; otherwise it returns “Not today.” =IF(A2=TODAY(),"Today","Not today") If A2 contains a time as well as a date, an exact equality test may fail because the time portion is not zero.
How do I ignore the time in a date-and-time value?
If A2 contains a date and time and you want to compare calendar dates only, use INT to remove the fractional time portion. With a date-time value in A2, this returns “Today” when its calendar date matches today, regardless of the time. =IF(INT(A2)=TODAY(),"Today","Not today") Add a blank check if A2 may be empty, so blank rows do not receive a label.
Can I return a number instead of words?
Yes. Replace the text results with numbers or cell references. For instance, you can return 1 when a date meets a condition and 0 when it does not. Choose outputs that downstream calculations can interpret consistently, and make sure the false result is intentional.
Why does my formula work on one computer but not another?
Date display conventions differ by region, and some Excel installations use semicolons instead of commas as formula separators. Use DATE(year,month,day) to avoid ambiguous typed date strings, and use the argument separator your Excel expects. Excel will often adjust formula separators when a workbook is opened in another regional setup.
Can I use an IF formula to highlight dates instead?
An IF formula returns a value in a cell; it does not itself apply color. To color dates based on a rule, use Home > Conditional Formatting and create a rule for the relevant cells. You can still use the same date logic, but set the conditional formatting rule rather than expecting the IF result to change a cell’s appearance.
Related
- How to Split a Page in Excel for Viewing or Printing
- How to Split Cells in Half in Excel: 3 Practical Methods
- Split Cells in Excel: Shortcut Key and Text to Columns
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Cell Range Average: Formula and ExamplesTo average a range in Excel, use AVERAGE: if A2:A5 contain 10, 20, a blank cell, and 0,…
- Age From DOB in Excel: DATEDIF Formula and ExamplesTo calculate age in completed years from a date of birth in Excel, enter the birth date in…
- Add a Sequence in Excel with the SEQUENCE FunctionTo add a numbered sequence in Excel, enter =SEQUENCE(10) in a blank cell to spill the numbers 1…
- Count Words in Excel: Formulas for Exact and Partial MatchesTo count every occurrence of a word across text in Excel, put the word to find in D1…
- Excel Formula: Years, Months, and Days Between DatesTo calculate complete years, months, and remaining days between dates in Excel, use DATEDIF for the years and…
- Excel VLOOKUP Examples With a Drop-Down ListTo make a drop-down selection drive a lookup in Excel for Microsoft 365 on Windows, create a list…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.