The core formula for conditional formatting based on a date in Excel is =A2=TODAY() (assuming your date is in cell A2), which highlights cells that match today’s date. For other date conditions, you replace TODAY() with functions like EDATE, DATE, or DATEDIF inside a formula rule. This article explains how to write, apply, and troubleshoot date-based conditional formatting formulas in Excel for Microsoft 365 on Windows, with notes for Mac and Excel for the web.
How Date Conditional Formatting Works
Conditional formatting applies formatting (fill, font, border) to cells when a condition is true. When you use a formula, Excel evaluates that formula for each cell in the selected range, relative to the active cell. The active cell is the first cell in your selection (usually the top-left cell), and you write the formula as if that cell were the reference point.
For example, if you select A2:A100 and the active cell is A2, the formula =A2>TODAY() will check each cell in A2:A100 against today’s date. Excel automatically adjusts the row reference for each row. This relative referencing is what makes formula-based rules powerful—you can compare dates to other cells, calculate age, or flag overdue items.
Setting Up a Formula-Based Rule
To create a formula-based conditional formatting rule:
- Select the range of cells that contain dates (e.g., A2:A100).
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter your formula in the Format values where this formula is true box.
- Click Format to set the fill, font, or border, then click OK.
On a Mac, the menu path is the same: Home > Conditional Formatting > New Rule. In Excel for the web, go to Home > Conditional Formatting > New Rule as well, but note that some advanced functions like EDATE work, while DATEDIF may not be available in all web versions.
Common Date Formulas for Conditional Formatting
Here are practical formulas with assumed layouts and results:
- Highlight today’s date (dates in A2:A20):
=A2=TODAY()– highlights only cells equal to today. - Highlight past dates (dates in A2:A20):
=A2– highlights all earlier dates. - Highlight dates within the next 7 days (dates in A2:A20):
=AND(A2>=TODAY(), A2<=TODAY()+7)– includes today and the next 7 days. - Highlight dates older than 30 days (dates in A2:A20):
=A2– flags dates more than a month old. - Highlight dates in the current month (dates in A2:A20):
=TEXT(A2,"YYYYMM")=TEXT(TODAY(),"YYYYMM")– compares year and month as text. - Highlight dates that are weekends (dates in A2:A20):
=WEEKDAY(A2,2)>5– where 6=Saturday, 7=Sunday. - Highlight dates that are overdue compared to a deadline column (start dates in A2:A20, deadlines in B2:B20):
=AND(A2– highlights start dates that are past and before the deadline.
All examples assume the active cell is A2. Adjust the cell reference if your selection starts elsewhere.
Choosing the Right Date Function
Excel has several date functions; the one you pick depends on the condition:
- TODAY() – returns the current date, recalculates when the workbook opens. Use for “today” comparisons.
- NOW() – returns current date and time. Use only if you need time precision; it can cause formatting to change mid-day.
- DATE(year, month, day) – creates a static date. Use for fixed deadlines like
=A2>=DATE(2025,1,1). - EDATE(start_date, months) – adds months to a date. Use for monthly renewals:
=A2<=EDATE(TODAY(),1)highlights dates within the next month. - DATEDIF(start_date, end_date, "d") – calculates days between dates. Use for age calculations:
=DATEDIF(A2,TODAY(),"d")>30flags dates more than 30 days old. - WEEKDAY(date, return_type) – returns day of week. Use for weekend or weekday highlighting.
Remember that DATEDIF is not available in Excel for the web; use =TODAY()-A2>30 instead for day counts.
Common Problems and Fixes
Several issues arise frequently with date conditional formatting:
- No formatting appears – Check that your dates are real dates, not text. Excel stores dates as numbers; text dates won't compare correctly. Select the cells and change the number format to Date to verify.
- Formula works in a cell but not in conditional formatting – In a cell, you might type
=A2=TODAY()and get TRUE/FALSE. In conditional formatting, the formula must be relative to the active cell. If you select A2:A10 but the active cell is A5, write=A5=TODAY(). - Whole row highlights instead of just the cell – If you want the entire row to highlight, use a formula that references the first cell in the row, e.g.,
=$A2=TODAY()(with a mixed reference) and apply the rule to the range A2:E100. - Dates appear as numbers after formatting – Conditional formatting doesn't change the number format; it only changes fill/font/border. If you need a date format, apply a number format separately.
- Rule doesn't update when today changes –
TODAY()recalculates when the workbook opens or when a calculation occurs. If you keep the workbook open overnight, press F9 to recalculate.
Comparing Formula Rules to Built-In Date Rules
Excel has built-in date rules under Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring. These offer quick options like “Yesterday”, “Today”, “Next week”, etc. However, these are limited to preset periods and cannot use custom logic like “within 30 days” or “between two specific dates”. Formula rules give you full control, but require more setup. As a rule of thumb, use built-in rules for simple, common cases (e.g., highlight today), and use formulas for anything beyond the presets.
Performance and File Size Considerations
Formula-based conditional formatting is recalculated each time the sheet changes, so excessive rules can slow down large workbooks. To keep performance reasonable:
- Limit the range to the actual data, not entire columns (e.g., A2:A1000 instead of A:A).
- Avoid volatile functions like
NOW()unless necessary;TODAY()is volatile too but less so. - Combine multiple conditions into one formula using
ANDorORrather than stacking separate rules. - Delete unused rules via Home > Conditional Formatting > Manage Rules.
These steps help keep your workbook responsive.
Frequently Asked Questions
How do I highlight a date if it's within 30 days from today?
Use =AND(A2>=TODAY(), A2<=TODAY()+30) for future dates, or =AND(A2<=TODAY(), A2>=TODAY()-30) for past dates. Adjust the range reference to your active cell.
Why does my conditional formatting formula work for one cell but not the whole range?
Most likely you wrote the formula with an absolute reference like =$A$2. For a range, use a relative reference (no dollar signs) so each row adjusts. If you want to highlight entire rows, use a mixed reference like =$A2.
Can I use conditional formatting to highlight dates that are between two dates I type in cells?
Yes. Suppose your start date is in D1 and end date in E1. With dates in A2:A20, use =AND(A2>=$D$1, A2<=$E$1). Use absolute references for D1 and E1 so they don't change per row.
How do I highlight blank date cells?
Use =ISBLANK(A2) for truly empty cells, or =A2="" for cells that contain an empty string. Note that ISBLANK returns FALSE for cells with a formula that returns "", so choose accordingly.
Does conditional formatting work with dates stored as text?
No, not reliably. Text dates are not recognized by date functions. Convert text to dates using the DATEVALUE function or the Text to Columns wizard (Data > Text to Columns > Dates). After conversion, the formula will work.
Safety and Accuracy Notes
Conditional formatting does not change the underlying data; it only affects appearance. However, be cautious when copying cells with conditional formatting—the rules may copy along with the cell, causing unexpected formatting in new locations. Always review rules via Home > Conditional Formatting > Manage Rules after copying. Also, remember that TODAY() and NOW() are volatile functions; they recalculate when the workbook opens or when any change occurs, which can cause formatting to update unexpectedly if you have many rules.
Related
- Excel Auto Loan Amortization Template: How to Build One
- Excel Attendance Point Tracker Template: Setup & Use
- Excel and Numbers Compatibility: What Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Basic IF Examples in Excel: How to Write the FormulaIn Excel, an IF formula checks whether a condition is true and returns one value if it is,…
- Excel SPLIT Function: Use TEXTSPLIT to Separate TextExcel does not have a function named SPLIT. In Microsoft 365 or Excel 2024, use TEXTSPLIT to divide…
- BMI Formula for Excel: Metric and ImperialFor a metric BMI calculation in Excel, put weight in kilograms in B2 and height in meters in…
- Excel Not Equal Symbol: How to Use <> in FormulasIn Microsoft Excel, the not-equal operator is <>—a less-than sign followed by a greater-than sign. Use it in…
- Excel F4: How to Lock Cell References in FormulasTo lock a cell reference in an Excel formula, edit the formula, place the cursor in or select…
- 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…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.