To compare dates in Excel, select the cells to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula that compares the first cell in the selected range with the date you want to check. For example, a rule can highlight dates in A2:A100 that fall before the cutoff in B1. Built-in date rules also work for simple conditions such as dates in the past or next month.
How date comparison rules work
Excel stores real dates as serial numbers, so comparisons such as earlier than, later than, or equal to work like numeric comparisons. The date’s display format does not determine the result: a cell showing 4/15/2026 can still be compared even if its underlying value is displayed as April 15, 2026.
Conditional formatting checks a rule against each cell in its Applies to range. A formula rule is written relative to the top-left cell of that range. If the range begins at A2, a reference to A2 adjusts as Excel evaluates the rule for A3, A4, and the remaining cells. This relative-reference behavior is why the starting cell in the formula must match the range’s first cell.
References can also be locked. A dollar sign fixes a column or row as the rule moves across the range. For a single cutoff date in B1, locking both parts of that reference keeps every comparison pointed at B1. The date cells, by contrast, generally need a relative reference so each row is checked independently.
Use built-in rules for straightforward comparisons
For a simple comparison against a date you enter yourself, select the date cells and use Home > Conditional Formatting > Highlight Cells Rules > Greater Than, Less Than, or Between. Enter the date or dates in the dialog, choose a format, and confirm. These rules are convenient when the comparison value is fixed and you do not need to refer to a cell elsewhere in the sheet.
Excel also offers Home > Conditional Formatting > Date Occurring. Its options include relative periods such as yesterday, today, tomorrow, last week, and next month. These are useful for calendars and task lists where the relevant date moves as time passes. They are not the same as comparing every date to a custom date stored in a worksheet cell; use a formula rule for that job.
On Excel for Microsoft 365 for Windows, choose the rule from the Home tab and set its formatting in the dialog. Excel for Microsoft 365 for Mac has the same basic conditional-formatting approach under Home > Conditional Formatting, though the dialog layout may differ. In Excel for the web, the Home tab also contains Conditional Formatting; some rule-management options and dialog controls can differ from the desktop application.
Compare a date column with a cutoff date
Suppose A2:A100 contains due dates and B1 contains a cutoff date. To highlight each date earlier than the cutoff, select A2:A100, create a formula rule, and use the formula below. Choose a fill or font color, then confirm. Because the cutoff reference is fixed, every date is compared with the same value.
With dates in A2:A100 and the cutoff date in B1, use =A2<$B$1. The rule highlights a cell when its date is earlier than the date in B1; a date equal to the cutoff is not highlighted.
For overdue tasks, it is common to compare each due date with today rather than with a manually maintained cutoff. TODAY returns the current date, so the rule updates as the calendar changes. If the column can contain blank cells, include a blank check so empty cells are not treated as old dates.
With due dates in A2:A100, use =AND(A2<>"",A2<TODAY()). It highlights nonblank dates earlier than today. If the rule should also highlight a due date that is today, change the comparison from less than to less than or equal to; decide which meaning of overdue fits your workflow.
Compare two date columns or highlight whole rows
Comparing two dates on the same row is useful for tracking whether a task finished on time, whether a renewal date precedes an end date, or whether a revised appointment differs from the original. For example, suppose column A has a planned date and column B has an actual date. The formula below highlights an actual date that came before the planned date.
With planned dates in A2:A100 and actual dates in B2:B100, select B2:B100 and use =B2<A2. The comparison is made row by row, so B2 is checked against A2, B3 against A3, and so on.
You can also color an entire record based on its date field. Suppose a task table occupies A2:F100 and its due dates are in column C. Select A2:F100, then make a formula rule that uses the due-date column while keeping the column fixed and the row relative. The first row of the selected range is row 2, so the reference must use row 2 in the formula.
With task data in A2:F100 and due dates in C2:C100, use =AND($C2<>"",$C2<TODAY()). This highlights the whole row when its due date is present and earlier than today. The dollar sign before C keeps the rule checking column C as it formats cells across the row; the row number changes for each record.
To identify matching dates in two columns, use an equality comparison; to identify dates that differ, use a not-equal comparison. If either date may be blank, add a nonblank test for both cells first. Otherwise, blanks can produce results that do not reflect the practical meaning of a completed comparison.
Highlight dates inside a range or rolling window
A fixed date range is useful for identifying appointments in a reporting period or deadlines within a project phase. If the start and end dates are stored in cells, you can adjust the period without editing the rule. The comparison below includes both boundary dates, which is usually what people mean by dates between two endpoints.
With dates in A2:A100, a start date in D1, and an end date in E1, use =AND(A2>=$D$1,A2<=$E$1). The rule highlights dates on or after D1 and on or before E1. If the endpoints should not count, use strict greater-than and less-than comparisons instead.
A rolling window can flag upcoming dates, such as the next seven days. With dates in A2:A100, use =AND(A2>=TODAY(),A2<TODAY()+7). This includes today and the following six calendar dates, but excludes the date exactly seven days from today. If you want to include that seventh day too, use a less-than-or-equal comparison for the upper boundary. Be clear about whether your window means seven calendar dates or a particular number of working days; the formula above counts calendar days.
Set the range, references, and rule priority carefully
After creating a rule, open Home > Conditional Formatting > Manage Rules to review the Applies to range and rule order. A rule intended for A2:A100 should not accidentally apply to the header or to unrelated columns. When copying data or inserting rows, review the range because it may need adjustment.
Check these details before relying on a comparison rule:
- Start cell: Make the formula’s relative reference match the top-left cell of the Applies to range.
- Fixed references: Lock a cutoff cell such as B1 if every row should compare against the same date.
- Row highlighting: Lock the date column but leave the row relative when the rule formats multiple columns.
- Rule order: If several rules apply to the same cells, their order and any Stop If True setting can affect which format appears.
- Formatting: Choose a readable fill or font color; a correct rule can be hard to notice if the chosen format is subtle or conflicts with another rule.
Conditional formatting changes appearance, not the cell’s value. It does not sort dates, change a deadline, or prevent someone from entering an invalid date. Use data validation or other controls if you also need to restrict entries.
Common problems and practical fixes
A date is ignored or compared incorrectly. The cell may contain text that looks like a date rather than an actual Excel date value. Changing the number format alone does not convert text into a date. Check whether the value behaves like a date in a normal calculation, and convert imported text using an appropriate method such as Text to Columns or a suitable date conversion formula. Regional settings can affect how ambiguous entries such as 03/04/2026 are interpreted.
Blank cells are colored. Add a nonblank condition to the formula when empty cells should be ignored. This is especially important for deadline columns that are only partly filled in. Also check whether a formula in the cell returns an empty string; the cell may look blank but still contain a formula.
A date that appears equal is still flagged. One cell may include a time as well as a date. Excel stores times as fractions of a day, so a value for noon is later than midnight on the same calendar date. If you want to compare calendar dates only, remove the time from the source data or adjust the rule’s logic to compare date-only values.
The rule works for the first row but not the others. Review the references and the Applies to range. A mistakenly locked row can make every cell compare against the same row, while a range beginning at row 3 paired with a formula based on row 2 creates an offset. Edit the rule rather than creating several nearly identical rules for each row.
The result does not change at midnight. TODAY updates when Excel recalculates the workbook, not through a continuously running timer. If a workbook remains open and the display seems stale, trigger recalculation or check calculation settings. Volatile date functions can also recalculate when the workbook recalculates, which is worth considering in very large or complex workbooks.
Frequently Asked Questions
Can conditional formatting compare a date with today?
Yes. Create a formula rule using TODAY and compare the date cell with it. Add a nonblank test if empty cells should remain unformatted. The rule updates according to the current date when Excel recalculates.
Can I compare dates in two different columns?
Yes. Use a formula rule that compares the two cells on the same row, such as planned and actual dates. Select the cells to format first, then ensure each reference in the formula corresponds to the correct column and starting row.
Can I highlight an entire row based on one date?
Yes. Select the full table range and write the formula using the date column with a fixed column reference and a relative row reference. That makes every cell in each row use the date from that row.
Does the date display format affect the comparison?
No, provided the cell contains a real Excel date value. A display format changes how the value looks, not the underlying date used in the comparison. Text that merely looks like a date is a separate issue and may need conversion.
Can I highlight dates from the past month or next week?
Yes. The Date Occurring built-in rule provides common relative periods, while a formula rule gives more control over exact boundaries. For a custom rolling period, compare the date with TODAY and specify whether the start and end dates are inclusive.
Why does the rule highlight a date that looks like today?
Check for a hidden time value, text-formatted dates, incorrect relative references, and the rule’s Applies to range. A cell can display only the date while retaining a time, which affects exact comparisons.
Related
- Can You Shuffle Names in Excel? Methods and Examples
- Can You Copy an Excel Spreadsheet? Methods Explained
- Can You Open the Same Excel File Twice?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add a Shared Mailbox in Outlook for Mac: Setup GuideTo add a shared mailbox in Outlook for Mac, open File > Open > Shared Mailbox, enter the…
- Record Audio in PowerPoint: A Practical GuideYes. In Microsoft PowerPoint for Microsoft 365 on Windows, record narration for slides from the Record tab using…
- Python pandas Excel Merge: Join Workbooks and SheetsTo merge Excel data with Python, read each worksheet into a pandas DataFrame with pd.read_excel(), then use pd.merge()…
- Create an Outlook Signature: Setup and Management TipsTo create a signature in Outlook, open the signature settings for your version, choose to add a new…
- XLOOKUP in Google Sheets: Syntax, Examples, and TipsTo look up a value in Google Sheets, use =XLOOKUP(search_key, lookup_range, result_range, "Not found"). For example, if product…
- Excel Page Break Shortcut: Insert, Remove, and ResetIn Excel for Microsoft 365 on Windows, press Alt+P, B, I to insert a page break: press the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.