To use IF with dates in Excel, test a cell containing a real date and return the label you want for each result. With a due date in A2, enter =IF(A2<TODAY(),"Overdue","Not yet due") in B2. If A2 contains November 2, 2026, and today is November 3, 2026, the result is “Overdue.” The formula updates as the current date changes. Before starting, make sure your date cells contain Excel-recognized dates rather than text.
What You’ll Need
- A worksheet in Microsoft Excel for Microsoft 365, with the dates you want to check in a column.
- A blank column for the IF results, such as dates in A2:A20 and results in B2:B20.
- A clear rule to test, such as whether a date is before today, falls within a particular month, or matches a specific date.
The instructions below are for Excel for Microsoft 365 on Windows. The IF function and formulas shown also work in Excel for Mac and Excel for the web. Ribbon layouts can differ slightly between platforms, but the formulas do not.
Step-by-Step
- Arrange your dates and results. Put one date in each row of a single column, with a heading such as Due Date in A1. Keep the cells beside them empty for the formula results. For example, you might use A2:A20 for due dates and B2:B20 for labels. Avoid merged cells within the list, because they can make filling a formula down the column less predictable.
- Check that Excel recognizes the entries as dates. Select a date cell and look at the formula bar. A real Excel date may display in different formats, but Excel stores it as a number and can compare it chronologically. To apply a readable display format, select the date cells and choose Home > Number Format, then select a date format. Formatting changes how a value appears; it does not turn text into a genuine date.
- Choose the condition in plain language. Decide exactly what should count as true. “Before today” is different from “today or earlier,” and “in November” requires both a start and an end boundary. Decide what the formula should show when the test is true and when it is false, such as Overdue versus Not yet due. This makes it easier to choose the comparison operator and avoid boundary mistakes.
- Enter a basic deadline formula. With a due date in A2 and the result in B2, enter
=IF(A2<TODAY(),"Overdue","Not yet due")in B2. TODAY returns the current date, so an earlier date is overdue; today and future dates return Not yet due. For example, with A2 set to November 2, 2026, and today November 3, 2026, B2 displays Overdue. If the date is November 3 or later, it displays Not yet due. - Decide whether the deadline day should count as overdue. The less-than sign tests whether the date is strictly earlier than today. If a date should count as overdue on the deadline day itself, use less than or equal to instead. With the same layout—due date in A2 and result in B2—enter
=IF(A2<=TODAY(),"Overdue","Not yet due"). If A2 is November 3, 2026, and today is November 3, 2026, the result is Overdue. Choose the rule that matches your process rather than changing the operator at random. - Test whether a date falls in a set period. For a fixed month, test both the first day and the first day of the following month. With a date in A2 and a label in B2, enter
=IF(AND(A2>=DATE(2026,11,1),A2<DATE(2026,12,1)),"November","Outside November"). If A2 contains November 15, 2026, the result is November; if it contains December 1, the result is Outside November. Using a less-than test for December 1 includes every time on November 30 if the cell happens to contain a time as well as a date. - Compare against one specific date. Use DATE to build a reliable date value in the formula instead of typing a date inside quotation marks. With the date being checked in A2 and the result in B2, enter
=IF(A2=DATE(2026,12,25),"Holiday","Other date"). If A2 contains December 25, 2026, the result is Holiday; December 24 returns Other date. DATE takes the year, month, and day as numbers, which avoids confusion between regional date formats such as month/day/year and day/month/year. - Handle blank cells before comparing dates. A blank can otherwise be treated like a very early value in a date comparison and may be labelled overdue. With dates in A2 and results in B2, enter
=IF(A2="","Enter a date",IF(A2<TODAY(),"Overdue","Not overdue")). If A2 is empty, B2 displays Enter a date; if A2 is November 2, 2026, and today is November 3, 2026, it displays Overdue. The outer IF checks for an empty cell first, while the inner IF evaluates a date only when one is present. - Fill the formula down the list. Select the cell containing the completed formula, then drag the small square at its lower-right corner (the fill handle) down the result column. Excel adjusts A2 to A3, A4, and so on for each row. Check at least one later row to confirm the reference moved correctly and the result matches that row’s date. If you prefer, copy the formula cell and paste it into the remaining result cells.
- Review the results and save. Verify a date before the cutoff, one on the cutoff, one after it, and a blank if your list can contain blanks. Confirm that the true and false labels are not reversed. Save the workbook after checking. If the result will be used for a recurring deadline, remember that TODAY can recalculate when the workbook is opened or recalculated, so a previously visible label may change over time.
Common Mistakes
- Entering dates as text. A value pasted with a leading apostrophe, or imported in a format Excel does not recognize, might look like a date but fail date comparisons. Re-enter it using a recognized date format or convert the source data before relying on the result.
- Using quotation marks around a date value. A comparison to text such as “11/3/2026” can be interpreted differently depending on regional settings. Use DATE(year,month,day) for fixed dates, or compare with a cell that contains a real date.
- Choosing the wrong comparison sign. Less than excludes the cutoff date; less than or equal to includes it. Greater than and greater than or equal to have the same distinction on the other side of a range. Test the exact boundary date to confirm the intended outcome.
- Forgetting that TODAY changes. A formula based on TODAY is not a permanent record of what was overdue on a past day. If you need a fixed historical status, use a fixed cutoff date or record the result as a value in a separate process.
- Leaving out one side of a date range. To identify dates within a month, check both the start and the end. A single test such as “date is after November 1” would also include dates in December and beyond.
Tips
- Use DATE for dates hard-coded into a formula. Its year, month, day order stays clear even when the computer uses a different regional date display.
- Keep result labels short and consistent. The text returned by IF can be used for filters, counts, or conditional formatting later.
- For a changing month or reporting period, put the start and end dates in worksheet cells and compare against those cells. This lets you update the period without editing every formula.
- If your data includes times as well as dates, decide whether the time should affect the result. A deadline set to midnight can behave differently from a deadline later in the day.
- In many regional settings, Excel uses commas between formula arguments. If Excel rejects a formula copied from another locale, your installation may expect semicolons instead; use the separator required by your Excel settings.
Troubleshooting
The formula returns an unexpected overdue result. First check whether the date cell is blank or contains text. Then confirm the comparison operator and whether the deadline itself should count as overdue. You can temporarily select the date cell and apply a standard date format to check that it displays as a date.
The formula shows the formula text instead of a result. The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, edit the formula, and press Enter. Also check that Show Formulas is not enabled; on Windows, Ctrl+` toggles formula display. On Mac, the equivalent shortcut is Control+`; if your keyboard layout differs, use the Formulas tab and the Show Formulas command.
Excel reports a formula error as you type. Check that every opening parenthesis has a closing parenthesis, text labels use straight double quotation marks, and the arguments use the separator your regional settings require. If you copied a formula from a website or document, replace curly quotation marks with straight ones.
A date range misses the last day of the month. Review the end condition. A test that uses less than the first day of the next month is a practical way to include all times on the last day of the month. If the worksheet has dates stored as text, correct those values before changing the range logic.
The status changes unexpectedly later. If the formula uses TODAY, a date that was in the future can become overdue as days pass. That is expected behavior for a live deadline check. For a fixed report, use a stated cutoff date rather than TODAY, or preserve a dated snapshot of the results.
Frequently Asked Questions
Can IF compare two dates in Excel?
Yes. Excel stores recognized dates as numeric values, so IF can test whether one date is earlier than, later than, or equal to another. Make sure both cells contain real dates, not text that only looks like a date.
How do I check whether a date is today?
Compare the date cell with TODAY using an equals test. Keep the date in a cell as a real date, and decide what the formula should return for a match and a non-match. TODAY updates as the current date changes.
Can I use IF to flag dates that are coming up soon?
Yes. Define the window you mean by “soon,” then test that the date is on or after today and no later than the end of that window. Use AND when both conditions must be true, and test the start and end dates themselves.
Why does my date comparison treat a blank as overdue?
A blank may behave like an early numeric value in a comparison. Test for an empty cell first, then evaluate the date only if the cell is not blank. This lets you show a helpful prompt instead of an incorrect status.
Does the IF formula work in Excel for Mac or the web?
Yes. IF, TODAY, AND, and DATE work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. The interface may look different, and the formula argument separator can depend on regional settings.
Related
- How to Share an Excel Workbook in Windows 11 Safely
- How to Share Excel in Google Drive: A Clear Guide
- How to Share Excel in Microsoft 365
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFERROR vs ISERROR: Which Should You Use?Use IFERROR when you want to replace an error with a value such as a message or blank-looking…
- Excel Find and Replace: Values, Formulas, and OptionsTo find and replace values in Excel, press Ctrl+H on Windows, enter the text or value under Find…
- COUNTIF Excel for Specific Text: Examples and TipsTo count cells that match specific text in Excel, use =COUNTIF(A2:A10,"Apple"); to count cells that contain the text…
- Excel LEFT Function: How to Extract Text from the StartTo return characters from the beginning of text in Excel, enter =LEFT(A2,3) in a cell, replacing A2 with…
- Excel IFERROR for Multiple Cells: Formulas and TipsTo handle errors across multiple cells in Excel, use IFERROR in the first output cell and fill the…
- Excel IF Formula Examples: Tests, Logic, and ErrorsExcel’s IF formula checks whether a condition is true or false and returns one result for each outcome.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.