In Excel for Microsoft 365 on Windows, use a formula-based conditional formatting rule to highlight dates that meet your criteria. Select the cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter a formula such as =AND(A2<TODAY(),A2<>"") to mark nonblank dates in A2:A100 that are before today, and choose a format.
How date-based conditional formatting works
A formula rule evaluates a formula for each cell in its applied range. When the formula returns TRUE, Excel applies the formatting you selected; when it returns FALSE, it does not. The formula is not entered into the worksheet cell itself. It is part of the rule, so the underlying date values remain unchanged.
Excel stores real dates as serial numbers and displays them according to the cell’s number format. This lets you compare dates with operators such as less than and greater than, or use functions such as TODAY. The display format is separate from the value: a cell may show “Nov 3” while still containing a full date that Excel can compare.
References in a rule usually need to be written for the first cell in the selected range. If the range is A2:A100, a reference to A2 adjusts as Excel evaluates the rule for A3, A4, and the other cells. A dollar sign makes a reference fixed: for example, $E2 keeps the column fixed while allowing the row number to change. This relative-versus-absolute reference behavior is central to rules that format whole rows.
Create a formula rule in Excel
- Select the cells the rule should affect. For a list of dates, this might be A2:A100. For whole-row formatting, select the full rows or columns you want formatted.
- On the Windows ribbon, choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Enter a formula that evaluates to TRUE or FALSE for the first cell in the selected range. Do not type a leading apostrophe.
- Choose Format, set the fill, font, border, or other formatting, and select OK to save the rule.
- To review or adjust it, open Home > Conditional Formatting > Manage Rules. Check that Applies to covers the intended range and that the formula references its top-left cell correctly.
For example, if A2:A100 is selected, write the rule using A2, not A1 or A100. If the selected range starts at A5, the formula should normally refer to A5. The formula’s reference and the Applies to range must agree; otherwise, the rule can appear to highlight the wrong dates.
In Excel for Mac, the corresponding ribbon route is also Home > Conditional Formatting > New Rule, though the dialog layout can differ. The Windows instructions above use the Microsoft 365 desktop interface.
Useful formulas for common date rules
These examples assume that the date values are in column A and that the selected range begins in row 2. Replace A2 with the first cell in your own selected range. Each formula belongs in the formula-rule box, not in a worksheet cell.
-
Highlight dates before today: With dates in A2:A100, use
=AND(A2<TODAY(),A2<>""). This marks past dates but leaves blank cells unformatted. A date equal to today is not treated as overdue. -
Highlight dates due today: With dates in A2:A100, use
=A2=TODAY(). This is suitable when the cells contain dates without times. If cells may contain times as well, see the time-stamp example below. -
Highlight dates from today through the next seven days: With dates in A2:A100, use
=AND(A2>=TODAY(),A2<=TODAY()+7). The range includes today and the date seven days from today. It does not include past dates. -
Highlight dates in the last 30 days, including today: With dates in A2:A100, use
=AND(A2>=TODAY()-30,A2<=TODAY()). This is a rolling 31-date span when both endpoints are counted. -
Highlight weekend dates: With dates in A2:A100, use
=AND(A2<>"",WEEKDAY(A2,2)>5). The second argument makes Monday day 1 and Sunday day 7, so values greater than 5 identify Saturday and Sunday. -
Highlight dates inside start and end dates entered in cells: With dates in A2:A100, the start date in F1, and the end date in G1, use
=AND(A2>=$F$1,A2<=$G$1). The dollar signs keep the input cells fixed as the rule evaluates each date in column A. Add a nonblank check if empty cells could otherwise be treated as part of the range.
TODAY updates as the workbook recalculates, so rules based on it move forward over time. That is useful for live deadlines, but it also means a workbook opened on a later date can show different highlights from the same rule.
Format a whole row based on its date
To shade a complete record when a date in one column is overdue, select the entire data area first. Suppose the table occupies A2:F100 and its due dates are in column E. With A2:F100 selected and A2 as the top-left cell, use =AND($E2<TODAY(),$E2<>""). Choose the desired format; Excel will apply it across each row whose E-column date meets the condition.
The dollar sign before E fixes the date column, while the row number remains relative. If the formula used E2 without the dollar sign, Excel could shift both the column and row as the rule moved across the selected range, causing other cells to be checked instead. If your selected area starts on row 5, use row 5 in the formula, such as $E5.
For a whole-row rule that marks dates due today, with A2:F100 selected and the relevant date in E2, use =$E2=TODAY(). If the date column may contain blank cells, an additional check can make the intent clearer: =AND($E2=TODAY(),$E2<>"").
Account for times and date ranges
A cell that looks like a date can also contain a time. Excel stores a time as a fraction of a day, so a value such as today at 3:00 p.m. is later than the date value for today at midnight. As a result, a direct equality check against TODAY may not match a timestamp later in the day.
To highlight any timestamp that falls on today, with timestamps in A2:A100 and the rule applied starting at A2, use =AND(A2>=TODAY(),A2<TODAY()+1). This checks the full interval from midnight today up to, but not including, midnight tomorrow. The same boundary approach is useful when filtering a day’s transactions or appointments.
For a calendar month, use boundary dates rather than relying on how a date is displayed. With dates in A2:A100, use =AND(A2>=DATE(YEAR(TODAY()),MONTH(TODAY()),1),A2<DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)). It includes dates from the first day of the current month up to, but not including, the first day of the next month. This form also handles months of different lengths without requiring you to enter each month’s final day.
Built-in date rules or formula rules?
For straightforward checks, Excel’s built-in conditional formatting options may be quicker. Select the date cells and look under Home > Conditional Formatting > Highlight Cells Rules or, depending on the task, Date Occurring. Built-in choices are convenient for common criteria and can be easier to maintain when another person will edit the workbook.
A formula rule is more flexible when the condition depends on another cell, several conditions, a particular rolling window, or a full row. For instance, a built-in rule may not express the exact combination of “date is between the two dates in F1 and G1, and the date cell is not blank” as directly as a custom formula. Use the simplest rule that accurately expresses the requirement; complexity can make a workbook harder to understand and troubleshoot.
Conditional formatting only changes appearance. It does not remove a date, sort the list, prevent an entry, or replace data validation. If a deadline must be enforced or records need to be filtered, use an appropriate worksheet feature in addition to highlighting.
Common problems and how to fix them
-
The rule highlights blanks. Blank cells can behave like zero in some comparisons. Include a test such as A2<>"" when the rule should apply only to entered dates. If cells contain formulas that return an empty string, the nonblank test generally excludes those displayed-empty results as well.
-
A date does not highlight even though it looks right. The entry may be text rather than a true Excel date. Try changing the cell’s number format to a date or checking whether Excel can recognize the value in a calculation. Re-enter or convert text dates when necessary; a date-looking string may not compare as a date.
-
The wrong row or column is formatted. Confirm that the formula starts from the top-left cell in Applies to. Use dollar signs only where a reference should stay fixed. In a row rule, lock the date column (for example, $E2) but generally leave the row number unlocked.
-
Today’s dates are treated as overdue. Check that the comparison is less than TODAY rather than less than or equal to it. Also check whether the cell contains a time and whether the intended condition is “before today” or “before this moment.”
-
Two formats compete. A cell may satisfy more than one conditional formatting rule. Open Manage Rules to review rule order, formats, and Applies to ranges. When rules overlap, priority and the Stop If True setting can affect which formats are displayed.
-
The formula is rejected. Check for balanced parentheses, straight quotation marks, and the correct first-cell reference. The examples use comma separators; some regional Excel settings display or require semicolons as function argument separators.
Frequently Asked Questions
What formula highlights overdue dates in Excel?
With dates in A2:A100 and the rule starting at A2, use =AND(A2<TODAY(),A2<>""). It highlights nonblank dates earlier than today, but not today’s date.
How do I highlight dates within the next seven days?
With dates in A2:A100 and the range starting at A2, use =AND(A2>=TODAY(),A2<=TODAY()+7). This includes today and the date seven days ahead; remove the lower-bound condition if past dates should also be included.
Can conditional formatting highlight an entire row based on a date?
Yes. Select the full table range and write the formula using a fixed date column and a relative row. For A2:F100 with dates in E, use =AND($E2<TODAY(),$E2<>"").
Why does TODAY not match a date that is displayed as today?
The cell may include a time, even if its number format hides it. For timestamps in A2:A100, use =AND(A2>=TODAY(),A2<TODAY()+1) to test whether the value falls anywhere during today.
Do conditional formatting date formulas change the date?
No. The rule changes only the cell’s appearance. It does not alter the stored date or its number format, and it does not prevent someone from editing the value.
Related
- Can You Lock One Tab in Excel? Sheet Protection Explained
- Can You Lock Zoom in Excel? Limits and Workarounds
- Cell Description in Excel: Meaning, Contents, and Notes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Structured References: Tables, Formulas, and ExamplesA structured reference is an Excel formula reference that identifies data in a table by its table and…
- Excel Filter Returns Numbers as Dates? Here’s WhyExcel’s filter feature does not turn numbers into dates; it either shows existing cell values or returns values…
- Excel Formula to Capitalize Names: What PROPER Gets RightTo capitalize names in Excel, enter =PROPER(A2) in a helper column when the name is in A2. PROPER…
- Excel 2019 SUBSTITUTE Function: Syntax, Examples, and TipsExcel 2019 includes the SUBSTITUTE function for replacing specified text inside a cell with different text. Its syntax…
- Excel Data Validation INDIRECT Formula: Dependent ListsIn Excel for Microsoft 365 on Windows, use =INDIRECT($A2) as the Data Validation list source in B2 when…
- Substitute Number for Letter in Excel: How to Do ItTo replace a letter with a number in Excel, use SUBSTITUTE: with the text to change in A2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.