Google Sheets Conditional Formatting for Dates

To highlight dates in Google Sheets, select the date cells, choose Format > Conditional formatting, and set a date rule such as “Date is today.” For a custom rule that highlights today’s date in A2:A, choose “Custom formula is” and enter =A2=TODAY(); the formula assumes dates begin in A2 and the conditional-formatting range begins there too.

What date conditional formatting does

Conditional formatting changes a cell’s appearance when its contents meet a condition. It can color overdue deadlines, mark upcoming appointments, or flag dates that fall within a chosen period. The date remains in the cell; the rule only changes how the cell looks. This makes the feature useful for calendars, project trackers, attendance sheets, and renewal lists where visual signals help readers scan information quickly.

Google Sheets stores valid dates as numeric values, even though they are displayed in familiar formats such as 11/3/2026 or Nov 3, 2026. That distinction matters: a cell that looks like a date may actually contain text. Date rules and formulas work reliably when the cell contains a real date value. The display format and the underlying value are separate, so changing a cell’s number format does not by itself create or repair a conditional-formatting rule.

Set up a built-in date rule

For common comparisons, start with the built-in criteria rather than writing a formula. The exact wording of the available date choices can vary with the rule menu, but it includes useful comparisons such as whether a date is before, after, or equal to a selected date, as well as relative choices such as today or tomorrow.

Select the cells containing dates, such as A2:A100. If you want a whole row to change color, select the full row range instead, such as A2:F100.
Open Format > Conditional formatting. The conditional-formatting panel appears on the right.
Check the Apply to range field and correct it if needed.
Under Format cells if, select an appropriate date condition, then set the date or relative option the rule should use.
Choose a text color, fill color, or other available style under Formatting style, then select Done.

Use a built-in rule when the condition is straightforward and you want to adjust it without maintaining a formula. For example, a “date is before” rule can flag an expired permit when you specify the comparison date. A relative rule such as “today” moves with the calendar, so it does not need to be edited every morning.

Use custom formulas for flexible date rules

Custom formulas are useful when a built-in condition cannot express the logic you need, or when a rule should depend on more than one test. In the conditional-formatting panel, choose Custom formula is and enter a formula that evaluates to TRUE or FALSE for the first cell in the selected range. Sheets adjusts relative references for the other cells.

For dates in A2:A, with the rule applied to A2:A, this formula highlights dates earlier than today:

=AND(ISNUMBER(A2),A2<TODAY())

ISNUMBER excludes blanks and text entries, while the comparison identifies dates before the current day. For dates in the next seven days, including today, use this formula with the same A2:A range:

=AND(ISNUMBER(A2),A2>=TODAY(),A2<=TODAY()+7)

TODAY() returns the current date, and adding 7 advances the comparison by seven days. If you want the next seven days but not today, change the first comparison to A2>TODAY(). These formulas compare actual date values rather than the way dates happen to be displayed.

Practical examples for calendars and deadlines

Choose a separate style for each meaningful status so the sheet communicates at a glance. For example, an overdue task might use a red fill, a deadline coming soon might use amber, and a date due today might use a stronger accent. Avoid assigning too many colors to conditions readers cannot distinguish or act on.

Highlight dates in the current month: With dates in A2:A, apply this custom formula to A2:A: =AND(ISNUMBER(A2),MONTH(A2)=MONTH(TODAY()),YEAR(A2)=YEAR(TODAY())). Checking both month and year prevents a date from the same month in another year from matching.
Highlight weekend dates: With dates in A2:A, apply this formula to that range: =AND(ISNUMBER(A2),WEEKDAY(A2,2)>5). Using 2 makes Monday day 1 and Sunday day 7, so values greater than 5 identify Saturday and Sunday.
Highlight today in a column containing timestamps: With date-and-time values in A2:A, apply this formula to A2:A: =AND(ISNUMBER(A2),INT(A2)=TODAY()). INT removes the time fraction before comparing the date.

These examples assume the first data row is row 2. If the data begins in row 5, use A5 in the formula and apply the rule starting at row 5. A custom formula is evaluated relative to the top-left cell of the range, so using the wrong starting row can shift the result by one or more rows.

Color an entire row based on its date

To shade a full task row according to a date in column A, select the rows and columns to format, for example A2:F100. Then create a custom formula based on the date cell in column A. With dates in A2:A100 and the rule applied to A2:F100, use:

=AND(ISNUMBER($A2),$A2<TODAY())

The dollar sign before A locks the formula to column A as Sheets applies the rule across columns B through F. The row number remains relative, so each row checks its own date. If you wrote A2 without the dollar sign, the reference could shift to other columns as the rule is applied across the row. This fixed-column, changing-row pattern is one of the most useful techniques for task trackers.

For a row to highlight when its date is today, use the same selected range and change the condition to =$A2=TODAY(). If column A can contain text or blanks, use =AND(ISNUMBER($A2),$A2=TODAY()) instead. The second version avoids treating a non-date entry as a match.

Dates, times, and regional settings

A date-time value includes a fractional part for the time. That is why a direct equality test against TODAY() may not match a timestamp from today: TODAY() represents the date at midnight, while a value such as today at 2:30 p.m. includes a time fraction. The INT-based example above removes that fraction. Alternatively, a range comparison can test whether a timestamp falls from today’s start up to, but not including, tomorrow.

Date interpretation can also depend on the spreadsheet’s locale. A value such as 04/05/2026 could mean April 5 or May 4, depending on the date convention in use. Check File > Settings for the spreadsheet’s locale if dates are being interpreted unexpectedly. For imported data, use an unambiguous date format or convert text into actual date values before relying on date rules.

Formula argument separators can also differ by locale. The examples here use commas, which are common in Google Sheets settings; some locales use semicolons instead. If Sheets reports a formula parse error even though the logic is sound, check the locale’s separator convention and replace commas with the appropriate separator.

Common problems and how to fix them

The rule highlights nothing: Confirm that the Apply to range begins on the same row referenced by the formula. Check whether the cells contain actual dates rather than text, and verify that the selected date condition matches the intended comparison.
Blank cells are highlighted as overdue: Add an ISNUMBER check to a custom formula, as in the examples. This limits the rule to numeric date values.
The wrong rows change color: Review relative and absolute references. Use A2 for a cell-by-cell rule on column A; use $A2 when a whole row should be controlled by column A.
A date shown as today does not match: The cell may contain a time as well as a date. Use INT on the value, or set a condition that covers the full day.
The rule appears inconsistent: Open the conditional-formatting panel and inspect all rules that apply to the same cells. Overlapping rules can make the final appearance confusing; simplify the conditions, change their formatting, or adjust their order.

Also distinguish the rule from the cell’s ordinary number format. If a date displays as a serial number, change its display using Format > Number and select a suitable date format. If it displays in an unexpected order, check the cell value and spreadsheet locale. Neither formatting change is a substitute for making text into a real date value.

Choosing between built-in rules and formulas

Use a built-in date condition when you need a simple comparison, such as before a chosen date or equal to today. These rules are easier to inspect and update in the sidebar. Choose a custom formula when you need a rolling window, a date plus another condition, a whole-row highlight, or a test that accounts for timestamps and blanks.

For a small one-off sheet, a built-in rule is often the clearest choice. For a reusable tracker, a custom formula can make the intended logic more explicit, especially if you label the columns clearly and keep the rule range consistent with the data. In either case, test the rule with a few known examples: one date before the boundary, one on it, one after it, and an empty cell if blanks are possible.

Google Sheets and Excel both support conditional formatting based on dates, and many of the formula concepts are similar. However, their interfaces and some available rule choices differ. These steps use Google Sheets in a web browser; Excel’s Conditional Formatting controls are found on its ribbon, so do not look for the Google Sheets Format menu in Excel.

Frequently Asked Questions

Can Google Sheets highlight dates that are overdue?
Yes. Select the date range, open Format > Conditional formatting, and choose a date-before condition or use a custom formula such as =AND(ISNUMBER(A2),A2<TODAY()) when dates begin in A2.

How do I highlight a row based on a date in one column?
Apply the rule to the whole row range, such as A2:F100, and use an absolute column reference with a relative row, such as =AND(ISNUMBER($A2),$A2<TODAY()). That makes each row check its own date in column A.

Will a conditional-formatting date rule update automatically?
Rules based on relative dates such as TODAY() update as the spreadsheet recalculates. A rule that compares dates to a fixed date will continue using that fixed comparison until you change it.

Why is a date stored as text not being highlighted?
A date-looking text string is not necessarily a date value. Check the cell’s content and locale, then convert or re-enter it as a real date. A custom formula using ISNUMBER can help ensure that only numeric date values are evaluated.

Can I highlight a date and time that falls today?
Yes. If the cells contain timestamps, compare the integer date portion with TODAY(), using a formula such as =AND(ISNUMBER(A2),INT(A2)=TODAY()) for values beginning in A2.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.