Yes. In Excel for Microsoft 365 on Windows, use conditional formatting with a formula that compares each date with TODAY(). With dates in A2:A100 and A2 as the first cell in the selected range, the rule =AND($A2<>"",$A2<TODAY()) highlights dates earlier than today while leaving blank cells unformatted.
Set up a rule for dates that have passed
For a simple list of deadlines, select the date cells, create a formula-based conditional formatting rule, and choose the format you want. These steps use the Windows desktop version of Microsoft 365:
- Select the date range, for example A2:A100. Do not include the heading unless you intend to format it too.
- Choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- With A2 as the top-left cell of the selected range, enter
=AND($A2<>"",$A2<TODAY()). - Choose Format, select a fill, font, or border style, and select OK to save the format and rule.
The dollar sign fixes the date column, while the row number remains relative. That lets Excel evaluate A2 for the first row, A3 for the next, and so on. If your selected range starts in a different row, update the formula’s row reference to match its top-left cell. On Excel for Mac, the rule is also available from Home > Conditional Formatting, although the dialog layout may differ.
What “past today” means in Excel
TODAY() returns the current date according to the computer’s date settings. Excel stores dates as serial numbers, so a comparison such as less than TODAY() checks whether a cell’s date value is earlier than the current date. A date equal to today is not earlier, and therefore is not highlighted by the rule above.
This is useful for overdue tasks: a deadline from yesterday or last month gets the format, while a deadline due today does not. If your policy considers anything due today overdue, use a less-than-or-equal comparison instead. With dates in A2:A100, this blank-safe formula includes date-only entries for today: =AND($A2<>"",$A2<=TODAY()).
Be clear about the rule you want before choosing the comparison. “Past today” usually means strictly earlier than today; “due today or earlier” includes today. The distinction is small in the formula but can change which tasks, invoices, or milestones stand out.
Highlight an entire row based on its date
You can color a whole record rather than just the date cell. Suppose each task occupies columns A through D, with its deadline in column C and the first task on row 2. Select A2:D100, then create a formula rule. Because C contains the dates, the formula must point to column C; the column is fixed so the rule checks that deadline for every cell in the same row.
With deadlines in C2:C100 and A2 as the top-left cell of the selected apply-to range, use =AND($C2<>"",$C2<TODAY()). Choose a format and save the rule. The dollar sign before C keeps Excel checking the deadline column as it applies the format across A, B, C, and D. The row number has no dollar sign so it changes for each record.
If you edit the rule later, check its “Applies to” range as well as its formula. For this example, it should cover the records, such as =$A$2:$D$100. A correct formula paired with the wrong range can make the formatting appear inconsistent or affect headings and unrelated cells.
Choose the right formula for your data
Use the simplest rule that matches how your workbook treats deadlines. These common variations assume the date or timestamp is in column A and the selected range begins at A2:
- Past dates, ignoring blanks:
=AND($A2<>"",$A2<TODAY()). This is a good general choice for a deadline list. - Today and earlier, ignoring blanks:
=AND($A2<>"",$A2<=TODAY()). Use it when today’s deadline should also be flagged. - Any time on or before today:
=AND($A2<>"",$A2<TODAY()+1). This version is useful if cells may contain times as well as dates.
The final version includes all timestamps from today because TODAY()+1 represents midnight at the start of tomorrow. It also includes earlier dates. It does not include a timestamp from tomorrow onward. If your cells contain date-only values, the first or second formula is usually clearer.
Understand dates that include times
Excel can store a date and time together as one serial value: the whole-number portion represents the date, and the fractional portion represents the time. This matters when comparing timestamps with TODAY(), which has no time component and corresponds to midnight at the start of the current day.
For example, a cell containing today at 2:00 p.m. is numerically greater than TODAY(). It will not match a less-than-or-equal-to-TODAY() rule, even though its calendar date is today. If the intended rule is to flag any timestamp whose calendar date is today or earlier, compare it with tomorrow’s date using the third formula above. For “strictly before today,” the less-than-TODAY() test already catches all times on previous days and excludes today’s timestamps.
Changing a cell’s display format to show only a date does not remove its stored time. If a same-day entry behaves unexpectedly, inspect the value or use a formula that explicitly allows the whole current day.
Why blank cells or dates may not format as expected
A common surprise is that a rule using only =$A2<TODAY() may format empty cells in the range. Excel treats a truly empty cell like zero in many numeric comparisons, and zero is earlier than today’s date serial number. Add a nonblank test, as in the examples, to avoid that result.
Other common causes are data quality or reference errors. Check these points if the rule seems not to work:
- Text that looks like a date: A value imported as text may not compare like a real Excel date. Try changing the cell’s number format or converting the source data to actual dates; changing appearance alone may not convert text.
- Wrong starting row: If the selected range starts at A5 but the formula refers to A2, Excel evaluates the wrong relative rows. Match the reference row to the range’s top-left cell.
- Wrong date column: For whole-row formatting, use the date column with a fixed column reference, such as $C2, rather than a relative reference that shifts across columns.
- Unexpected “Applies to” range: Open Conditional Formatting > Manage Rules and confirm the rule covers the intended cells.
- Another rule overrides the appearance: In Manage Rules, inspect rule order and whether Stop If True is enabled. Other conditional formats may assign a competing fill or font style.
It can help to test the rule on a few known values: one date from yesterday, one for today, one for tomorrow, and a blank. That quickly reveals whether the comparison and blank handling match your intention.
How TODAY updates, and when it may not
TODAY() is a volatile Excel function, meaning Excel recalculates it as part of workbook recalculation. A conditional formatting rule using TODAY() can therefore move dates between past, present, and future as days change without you editing each cell. It is not a permanent record of when a task became overdue; it reflects the current date at recalculation.
If the workbook stays open across midnight, the displayed result may not change at the exact moment midnight passes. Excel updates based on calculation, and calculation settings or workbook activity can affect when the result refreshes. Check Formulas > Calculation Options and make sure calculation is set to Automatic if you expect routine recalculation. Press F9 on Windows to recalculate; on Mac, use Fn+F9 on keyboards where function keys require the Fn key. Reopening a workbook also causes recalculation in normal automatic-calculation use.
TODAY() uses the computer’s system date, not a date independently supplied by the workbook. If the computer date is wrong, the conditional formatting comparison will be wrong too. For a shared process that requires a fixed reporting date, enter that date in a designated cell and refer to it instead, so readers can see and control the comparison date.
Alternatives to conditional formatting
Conditional formatting changes the appearance of cells; it does not change their values or remove overdue items. To find them, you can filter a date column or sort it from oldest to newest. A filter is often better when you need to work through overdue records, while conditional formatting is convenient when you want the full list visible with overdue items easy to spot.
A helper column is another option when users need a visible status label. With deadlines in A2:A100 and a status formula entered in B2, use =IF(A2="","",IF(A2<TODAY(),"Overdue","Not overdue")), then fill it down. For a date of yesterday, the result is “Overdue”; for today or a future date, it is “Not overdue.” This formula treats today as not overdue and leaves blank deadlines blank. You can adjust the comparison if your definition includes today.
Unlike color alone, a text status can be filtered, counted, or used in other formulas. However, it still depends on TODAY() and recalculation. If the goal is simply to draw attention to dates in an existing table, the conditional formatting rule keeps the data structure simpler.
Frequently Asked Questions
How do I highlight dates before today in Excel?
Select the date range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a blank-safe comparison such as =AND($A2<>"",$A2<TODAY()). Replace A2 with the first date cell in your selected range.
How do I include dates due today?
Use less than or equal to TODAY() instead of less than TODAY(). Add the nonblank test if empty cells are in the range. If cells include times and all timestamps on today’s date should count, use a comparison with TODAY()+1.
Will the formatting update automatically tomorrow?
It updates when Excel recalculates the workbook. Automatic calculation is the usual setting, but a workbook left open or set to manual calculation may not refresh at the moment the date changes. Confirm the calculation setting or recalculate the workbook.
Why are blank cells highlighted as overdue?
A direct comparison may treat an empty cell like zero, which is earlier than today’s date serial number. Add a test that the date cell is not blank, such as the AND condition shown above.
Can I highlight a whole row when its deadline has passed?
Yes. Select the rows or table range you want formatted, then create a formula rule that locks the date column but leaves the row relative. For deadlines in C2:C100 and a selected range beginning at A2, use =AND($C2<>"",$C2<TODAY()).
Related
- Contact List Excel Sheet: How to Build and Use One
- Contact List Excel Spreadsheet: Create and Manage It
- Conditional Formatting for Errors in Excel: How to Do It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Add Timestamp When a Cell ChangesYes. To add a fixed timestamp when a cell changes in Google Sheets, use a small Apps Script…
- Excel Geographic Heat Map Template: How to Make OneYes. In Excel for Microsoft 365 on Windows, make a geographic heat map with a table containing a…
- Merge Cells in Google Sheets: Web and App StepsTo merge cells in Google Sheets, select a rectangular range, then choose Format > Merge cells and pick…
- How to Compare Two Excel Sheets in Excel for DifferencesTo compare two Excel sheets cell by cell, add a comparison sheet and enter =IF(Sheet1!A1=Sheet2!A1,"Match","Different"), replacing the sheet…
- Excel Solver: Add Decision Variables and Set ConstraintsYes. To add variables in Excel Solver, put each decision variable in a worksheet cell and include those…
- Merge Cells in Excel: Keyboard Shortcuts and StepsIn Excel for Microsoft 365 on Windows, select the cells and press Alt+H, M, C—one key sequence at…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.