To highlight past-due dates in Excel, select the cells or rows you want to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =AND($B2<>"",$B2<TODAY()). This example assumes due dates are in column B and the first data row is row 2; choose a fill color, then select OK.
How Excel identifies a past-due date
Excel stores valid dates as numbers, with each date represented by a serial value. That makes it possible to compare a due date with the current date using a formula. TODAY() returns the current date, without a time of day, and the less-than operator checks whether a due date comes before it. A date before today is past due; a date equal to today is due today, not past due.
The AND function in the example adds a blank check. Without it, an empty due-date cell can sometimes be treated as zero in a comparison and receive the past-due format even though no deadline was entered. The condition $B2<>"" means the cell in column B is not blank. Together, the two tests highlight only rows with a nonblank date earlier than today.
Conditional formatting changes how a cell appears; it does not alter the date, add a status value, or move a task to another list. The rule is evaluated again as the workbook recalculates. Because TODAY() is a dynamic function, a date that is not overdue today can become overdue on a later day without anyone editing that cell.
Create a rule for past-due dates
In Excel for Microsoft 365 on Windows, create a formula-based rule as follows:
- Select the cells you want to format. To color just the due dates, select B2:B100 or the relevant range in column B.
- On the ribbon, choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Enter the formula below, using the column and first row that match your worksheet.
- Select Format, choose a fill, font, or border style, and confirm with OK. Select OK again to create the rule.
For due dates in B2:B100, this formula highlights dates before today and leaves blank cells alone:
With due dates in B2:B100 and the first data row at row 2, use =AND($B2<>"",$B2<TODAY()). For example, if today is June 10 and B2 contains June 9, B2 meets the condition; if it contains June 10 or June 11, it does not.
The dollar sign fixes the column reference at B while leaving the row number relative. This matters when one rule formats many rows: Excel evaluates B2 for the first row, B3 for the next, and so on. If your selected data begins in row 5, use B5 in the formula instead. The formula’s row should correspond to the first row in the selected range.
Highlight an entire row, not just the date
To make an overdue task easier to spot, you may want to shade the whole record rather than only its due-date cell. Select the table’s data area, such as A2:D100, before creating the formula rule. Keep the due-date column fixed with dollar signs and keep the row relative:
With records in A2:D100, due dates in column B, and the first record on row 2, use =AND($B2<>"",$B2<TODAY()). The rule checks column B for each row and applies the selected style across columns A through D on any row that qualifies.
Check the rule’s Applies to range after creating it. Open Home > Conditional Formatting > Manage Rules and select the relevant worksheet in the rule display if needed. The Applies to box should cover the cells you intend to shade. If it includes only column B, the rest of the record will remain unchanged; if it starts one row too early, the formatting may appear on the wrong record because the relative row reference no longer lines up.
Decide whether today counts as overdue
“Past due” usually means the deadline has passed, so a due date equal to today is not highlighted. If your process considers items overdue on the due date itself, change the comparison from less than to less than or equal to. This is a policy choice, not a difference in how Excel stores dates.
With due dates in column B and the first data row at row 2, use =AND($B2<>"",$B2<=TODAY()) to highlight nonblank dates that are today or earlier. If the current date is June 10, a June 10 due date will be highlighted by this version, while the earlier formula would leave it unformatted. Be consistent about this rule in reports so users understand whether “due today” and “overdue” are being combined.
You can also create separate formats for different levels of urgency. For example, one rule can flag overdue items in red and another can flag dates due within the next few days in amber. When rules overlap, use Manage Rules to review their order and settings. The exact result depends on the conditions and whether Stop If True is enabled for a rule; avoid overlapping rules unless you have decided which format should take priority.
Handle dates that include a time
A cell can contain both a date and a time, even if its number format displays only the date. TODAY() represents midnight at the start of the current day. Therefore, a deadline stored as today at 9:00 a.m. is later than TODAY() and will not meet a less-than-TODAY() test. This is normally appropriate when the rule is intended to flag dates from previous days, regardless of the time.
If deadlines include specific times and an item should become overdue as soon as its deadline passes, compare the value with NOW(), which returns the current date and time. With date-time deadlines in B2:B100 and the first data row at row 2, use =AND($B2<>"",$B2<NOW()). A deadline of 9:00 a.m. will qualify after 9:00 a.m. on that date, but not before. NOW() updates when Excel recalculates; it is not a continuously running clock, so the display may not change at the exact deadline until recalculation occurs.
Check why the rule is not working
The most common issue is that a value looks like a date but is actually text. Text dates may not compare as expected, particularly when imported from another system or interpreted using a different regional date order. Test a suspicious cell by changing its number format to General. A real Excel date typically displays a serial number; a text value remains text. You can also use Excel’s conversion tools or re-enter the value as a date, taking care not to change the intended day and month.
Other useful checks include:
- Confirm the first row reference. If the selected range begins at row 3, the formula should refer to row 3, not row 2.
- Inspect Applies to. The rule may be valid but applied to a different range than the cells you are looking at.
- Look for another rule. In Manage Rules, check for rules that apply to the same cells and use a conflicting fill or Stop If True.
- Check for hidden spaces or formulas returning empty text. A cell containing a formula that returns an empty string is not necessarily the same as a truly empty cell in every test. If your data uses formulas for blank-looking results, verify the rule against those rows.
- Verify calculation settings. If calculation is set to Manual, dynamic dates may not update when expected. On Windows, check Formulas > Calculation Options > Automatic.
Regional settings can affect how a typed date is interpreted, but they do not change the logic of the comparison once Excel has stored a valid date value. When in doubt, check the cell’s underlying value and use an unambiguous date entry method for your locale.
Conditional formatting versus a status formula
Conditional formatting is useful when the goal is visual scanning: it can color a due date or an entire row without adding a helper column. A status formula is better when you need the words “Overdue,” “Due today,” or “Upcoming” for filtering, sorting, exporting, or counting. These approaches can be used together, but they solve different problems. Formatting alone does not create a text label that can be used as a field in a report.
For a visible status column, you could enter a formula in C2 and fill it down. With due dates in B2 and a blank result desired when no date is present, use =IF(B2="","",IF(B2<TODAY(),"Overdue","Not overdue")). A date before today returns Overdue; a date today or later returns Not overdue. This simple version does not distinguish due today from future dates, so a separate nested condition would be needed if that distinction matters. Remember that the status is calculated from the date; it does not replace or modify the due date itself.
Excel for Mac and Excel for the web
In current Excel for Mac, the same formula logic applies. The Conditional Formatting commands are available from the Home tab; choose Conditional Formatting and create a new rule using a formula-based option. Ribbon wording or dialog layout can differ slightly by platform and update, but the key requirements remain the same: select the intended range, enter a formula based on its first row, and choose a format.
Excel for the web also supports conditional formatting, though some rule-management options and dialog details may differ from the Windows desktop app. If you cannot find a specific control or need to review complex overlapping rules, open the workbook in the desktop version of Excel. Do not assume a Mac or browser keyboard shortcut is identical to Windows; menu navigation is the clearest way to follow these steps across platforms.
Frequently Asked Questions
How do I highlight dates older than 30 days? For due dates in B2:B100, use =AND($B2<>"",$B2<TODAY()-30). It highlights dates more than 30 days before today. If you want dates exactly 30 days old included, use a less-than-or-equal comparison instead.
Can I highlight overdue dates in red? Yes. When creating or editing the rule, select Format and choose a red fill or font color. The color is only a visual cue; it does not change the cell value or send an alert.
Why are blank cells highlighted? A rule that compares a date cell directly with TODAY() may treat empty cells as values that satisfy the comparison. Add a nonblank test, such as the AND check in the examples, and make sure the date column is the one referenced.
Will the formatting update tomorrow automatically? It updates when Excel recalculates the workbook. TODAY() is a dynamic date function, but the workbook must recalculate for the displayed result to reflect a new day. Automatic calculation is the usual setting; Manual calculation can delay the change.
Can I highlight overdue dates while excluding completed tasks? Yes. Add a second condition for the completion field. For example, if due dates are in B and status is in C, a rule can check that B is not blank, B is before today, and C is not “Complete.” Use the exact status text and adjust the column references to match your worksheet.
Related
- Close Excel Shortcut Key for Windows and Mac
- Close Group Excel Shortcut: Close Workbooks and Rows
- Close Excel Shortcut: Windows and Mac Keys
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Edit Checkboxes in Google SheetsTo edit a checkbox in Google Sheets, select its cell and open Data > Data validation. Edit the…
- Automatic Timestamp in Google Sheets: How to Set It UpTo add an automatic, permanent timestamp in Google Sheets, use a small Apps Script that writes the current…
- How to Add an Image Box to an Excel Data Entry FormTo add an image box to an Excel data-entry form, create or open a VBA UserForm, place an…
- Excel Workbook Protection: What It Does and How to Use ItExcel workbook protection is a set of separate controls, not one all-purpose security switch. In Microsoft 365 for…
- How to Add a New Item to an Excel Dropdown List Step by StepTo add a choice to an Excel dropdown, update the list that supplies its options, then confirm the…
- How to Move Cells Down in Excel Without Overwriting DataTo move cells down in Excel and make room, select the cells that should shift, choose Home >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.