To conditionally format dates in Excel for Microsoft 365, select the date cells, then choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring to highlight dates such as today or last week. For more control, choose Home > Conditional Formatting > New Rule and use a formula such as =AND($A2<>"",$A2<TODAY()) to flag past dates in a range starting at A2. Conditional formatting changes a cell’s appearance when a rule is true; it does not change the date itself.
What date-based conditional formatting does
Conditional formatting applies visual cues—such as a fill color, font color, or border—to cells that meet a condition. A date rule can make a due date stand out when it is approaching, identify records entered during a particular period, or distinguish overdue items from future deadlines. Because the formatting is tied to a rule, it can update as dates change or as time moves forward.
Excel stores valid dates as serial numbers, with a time value optionally stored as a fraction of a day. Date-based rules compare those underlying values, not just the way they look on screen. A cell displayed as “Nov 2, 2026” might contain a time as well as a date, for example. This distinction matters when a rule checks whether a date is exactly equal to today.
Rules work on individual cells or on a range, and a worksheet can have several rules at once. That makes it possible to use one color for dates due today, another for dates coming soon, and a third for overdue work. The key is to choose a rule that matches the question you want the spreadsheet to answer.
Quick start with Excel’s built-in date rules
For common time periods, use the built-in date-occurring rule rather than writing a formula. On Windows, select the cells containing dates and go to Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. Choose a period in the dialog, select a formatting style, and click OK. Excel applies the rule to the selected range.
The available choices cover useful relative periods, including yesterday, today, tomorrow, the last seven days, and calendar weeks or months. These options are convenient when a whole column should be marked based on where each date falls relative to the current date. They are also easier to maintain than a custom formula when the built-in periods express exactly what you need.
To change the appearance or adjust the range later, use Home > Conditional Formatting > Manage Rules. In the rules manager, select the rule, edit it, and check the “Applies to” range. If multiple rules could apply to the same cell, their order and the “Stop If True” setting can affect the final appearance. Excel for Mac has the same general Conditional Formatting commands on the Home tab, though dialog details can differ by release.
Use formulas for more precise date conditions
A formula-based rule is useful when the built-in choices do not fit, such as highlighting the next seven days but excluding blank cells. Select the cells to format, choose Home > Conditional Formatting > New Rule, select “Use a formula to determine which cells to format,” enter the formula, choose a format, and confirm. The formula must return TRUE for a cell that should receive the format.
For the following examples, dates are in A2:A100, and the rule is applied to A2:A100. In the formula, the row number is relative so Excel checks each row in turn; the dollar sign before A keeps the date column fixed. Enter the formula as shown for a rule whose first selected cell is A2.
- To mark dates before today while ignoring blanks, use
=AND($A2<>"",$A2<TODAY()). A date of Nov 1, 2026 is marked when today is Nov 2, 2026; a blank cell is not. - To mark dates exactly today when the cells contain dates without times, use
=AND($A2<>"",$A2=TODAY()). A cell containing Nov 2, 2026 matches when today is Nov 2, 2026. - To mark today even when a cell may contain a time, use
=AND($A2>=TODAY(),$A2<TODAY()+1). This includes any time on today’s date and excludes midnight tomorrow.
TODAY() returns the current date according to Excel’s calculation environment. It updates when the workbook recalculates, so a rule based on TODAY() can change appearance as days pass without editing the date cells. If calculation is set to Manual, the display might not refresh until recalculation; check Formulas > Calculation Options if date-based formatting seems stale.
Highlight upcoming dates and date ranges
“Due soon” can mean different things in different workbooks, so set the boundary explicitly. With dates in A2:A100 and the rule applied to that same range, the following formula includes today and the next six calendar days: =AND($A2>=TODAY(),$A2<TODAY()+7). If today is Nov 2, the range runs from Nov 2 through Nov 8. The upper comparison is less than Nov 9, rather than less than or equal to Nov 9.
For a future-only warning that excludes today, use =AND($A2>TODAY(),$A2<=TODAY()+7) with the same A2:A100 layout and applies-to range. That checks dates after today through seven days from today, inclusive. Pick one definition and label the rule clearly, because “within seven days” is often interpreted differently by different readers.
You can also compare each date with start and end dates stored in cells. Suppose the dates to format are in A2:A100, the period’s start is in B1, and its end is in C1; apply the rule to A2:A100. Use =AND($A2<>"",$A2>=$B$1,$A2<=$C$1). This includes both boundary dates, so dates from Nov 1 through Nov 30 are highlighted if B1 contains Nov 1 and C1 contains Nov 30. Keeping the boundaries in cells makes it easy to change the reporting period without editing the rule.
For dates in the current calendar month, with dates in A2:A100 and the rule applied to A2:A100, use =AND($A2>=EOMONTH(TODAY(),-1)+1,$A2<=EOMONTH(TODAY(),0)). EOMONTH returns the last day of a month; adding one to the prior month’s end gives the first day of this month. This is different from “the next 30 days,” which can cross into the following month.
Format weekends, weekdays, and specific days
Sometimes the relevant condition is the day of the week rather than the date’s age. With dates in A2:A100 and the formatting range also A2:A100, use =AND($A2<>"",WEEKDAY($A2,2)>5) to highlight Saturdays and Sundays. In WEEKDAY, the second argument of 2 numbers Monday as 1 through Sunday as 7, so values greater than 5 identify the weekend.
To highlight one recurring weekday, use =AND($A2<>"",WEEKDAY($A2,2)=1) for Mondays, using the same A2:A100 layout and applies-to range. Change 1 to 2 for Tuesday, continuing through 7 for Sunday. This can help with shift schedules, delivery calendars, or weekly reporting, but it does not account for public holidays; those need a separate holiday list and an appropriate rule.
Apply rules to status tables and mixed data
In a task tracker, the date may be in one column while the status is in another. Suppose due dates are in A2:A100, status text is in B2:B100, and the rule should color the due-date cells A2:A100. Use =AND($A2<>"",$A2<TODAY(),$B2<>"Complete"). The dollar sign fixes each referenced column, while the row adjusts. A past date is highlighted only if the corresponding status is not Complete.
If the goal is to shade the entire task row, select A2:B100 before creating the rule and use the same formula. Excel evaluates the formula from the first row of the selected range, then applies the resulting format across that row. In a wider table, make sure the formula’s row number matches the first selected row; a mismatch can make the colors appear shifted by one row.
When using a formatted Excel Table, you can still create conditional formatting rules, but ordinary cell references are often the clearest choice in the rule formula. Verify the “Applies to” range after adding rows or changing the table. A rule that was applied only to a fixed range such as A2:A100 may not cover records added beneath it, depending on how the range and table were set up.
Common problems and how to fix them
If a date rule misses cells that look like dates, the values may actually be text. Imported data and dates entered in an unrecognized format are common causes. Changing the number format alone does not convert text into real dates. Test a suspicious cell with a simple calculation or inspect whether Excel recognizes it as a date; then convert the source values using an appropriate method, such as Text to Columns or a corrected import, before relying on date comparisons.
A rule that marks blanks as overdue usually needs an explicit blank check. In Excel, a blank cell can behave like zero in some comparisons, and zero is earlier than today. Add a condition such as $A2<>"" inside AND, as in the examples above. Also check for formulas that return an empty string: the blank test shown generally excludes an empty-string result, but test the rule against the actual data in your sheet.
Another common issue is using the wrong reference style. If the rule applies to A2:A100, a formula starting with A1 checks a different row than expected. If the rule applies across several columns but should always inspect dates in column A, use $A2, not A2; this keeps the column fixed while allowing the row to change. Use the Manage Rules dialog to inspect the formula and its applies-to range together.
Dates with times can also create surprising results. A cell showing Nov 2 may contain Nov 2 at 3:00 p.m.; it is greater than the date serial for midnight on Nov 2, so an equality test against TODAY() is false. Use a lower and upper bound for the day, such as A2 greater than or equal to TODAY() and less than TODAY()+1, when times are possible.
What conditional formatting can and cannot do
Conditional formatting is a visual aid, not a data-cleaning or reminder system. It does not alter the stored date, prevent someone from entering an invalid date, send a notification, or guarantee that a deadline will be noticed. Use data validation for input restrictions and a task or notification system when a deadline needs an alert outside the workbook.
Rules can also become difficult to maintain if many overlapping formats accumulate. Prefer a small set of distinct conditions, such as overdue, due soon, and later, and review their priority if the same cell can meet more than one condition. Keep the formatting meaningful—for example, use a text label as well as color if readers may have difficulty distinguishing colors. Conditional formatting is available in Excel for the web too, but advanced rule management and some dialog details may differ from the desktop application.
Frequently Asked Questions
Can Excel automatically highlight dates that are overdue?
Yes. Apply a formula rule to the date cells that tests whether each nonblank date is earlier than TODAY(). The formatting updates on recalculation, but it does not send an alert or change the underlying date.
How do I highlight dates within the next 7 days?
Use a formula rule with a lower bound of TODAY() and an upper bound of TODAY()+7. Decide whether today should count; a less-than upper bound includes today and the following six dates, while a future-only rule can exclude today.
Why is conditional formatting not recognizing my dates?
The cells may contain text rather than Excel date values, or the rule may reference the wrong row or range. Convert text dates into real dates, then check the rule formula and its “Applies to” setting.
Will a TODAY() rule update tomorrow?
It updates when Excel recalculates the workbook. TODAY() is a changing date function, so a rule can move from one day to the next without changing the source dates. If calculation is Manual, recalculate the workbook or change calculation settings.
Can I highlight a whole row based on its due date?
Yes. Select the rows you want formatted, then create a formula rule that checks the date column and uses a relative row reference. Fix the date column with a dollar sign, such as $A2, so every cell in a row is judged by that row’s due date.
Related
- Excel Shortcut to AutoFit Column Width on Windows and Mac
- Excel Shortcut to Make All Cells Fit Text Automatically
- Excel Shortcut to Repeat the Last Command or Action
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Microsoft Word Styles: How to Use ThemMicrosoft Word styles are reusable sets of formatting for text, paragraphs, lists, and other document elements. To use…
- Excel Shortcut Keys Interview Questions and AnswersExcel shortcut-key interview questions usually test whether you can move around a worksheet, edit and format data, and…
- Outlook Email Archive: How to Save Messages SafelyTo archive an email in Outlook, select the message and choose Archive on the toolbar or ribbon; Outlook…
- Google Sheets Weekly Timesheet Template: Setup TipsYes. You can make a weekly timesheet template in Google Sheets by creating a table for each day,…
- Excel Hide Specific Columns: Steps and TipsTo hide specific columns in Excel for Microsoft 365 on Windows, select the column letter or letters, then…
- Google Sheets FILTER Function: Examples, Syntax, and TipsIn Google Sheets, the FILTER function returns only the rows or columns from a range that meet conditions…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.