Date-based formatting in Google Sheets - Step-by-Step Guide

Introduction

Date-based formatting helps you change how cells look depending on the dates they contain. This makes it easy to spot important dates like deadlines or upcoming events in your spreadsheet.

When you want to highlight all dates that are past today to see overdue tasks.

When you need to mark upcoming birthdays within the next week in your contact list.

When you want to color-code dates that fall on weekends to plan work schedules.

When you want to show all dates in the current month with a special color.

When you want to highlight holidays or special events automatically based on date.

Steps

Step 1: Select the cells containing dates you want to format

- Spreadsheet grid

The selected cells are highlighted

💡 Click and drag to select a range or click the column letter to select a whole column

Step 2: Click Format

- Top menu bar

Format menu opens

Step 3: Select Conditional formatting

- Format menu

Conditional format rules pane opens on the right side

Step 4: Under Format cells if, click the dropdown and choose a date-based rule like 'Date is before', 'Date is after', or 'Date is exactly'

- Conditional format rules pane

The rule options update to let you specify the date condition

Step 5: Enter the date or use formulas like =TODAY() to set dynamic dates

- Value or formula input box in the conditional format rules pane

The rule is set to compare dates in the selected cells

Step 6: Choose a formatting style like fill color or text color

- Formatting style section in the conditional format rules pane

The preview shows how the cells will look if they meet the condition

Step 7: Click Done

- Conditional format rules pane

The formatting is applied to the selected cells based on the date condition

Before vs After

Before

Column A shows dates like 2023-05-01, 2023-06-15, 2023-07-20 with no special colors

After

Dates before today are highlighted with red fill color, making overdue dates easy to see

Settings Reference

Format cells if

📍 Conditional format rules pane

Choose the date condition that triggers the formatting

Default: None

Formatting style

📍 Conditional format rules pane

Select how the cells will look when the condition is true

Default: No formatting

Common Mistakes

Using text format cells instead of date format

Date-based rules only work correctly if cells are recognized as dates, not text

Make sure cells are formatted as Date by selecting Format > Number > Date before applying conditional formatting

Typing dates in the wrong format in the rule

Google Sheets expects dates in a standard format or as formulas like =TODAY()

Use the date picker or formulas like =TODAY() to avoid format errors

Not selecting the correct range before applying the rule

The formatting will not apply to the intended cells if the wrong range is selected

Carefully select the exact cells or column that contain the dates before starting conditional formatting

Summary

Date-based formatting changes cell appearance based on date conditions to highlight important dates.

Use built-in date rules or custom formulas to create dynamic date highlights.

Ensure cells are formatted as dates and select the correct range for the formatting to work properly.

Practice

1. What does date-based formatting in Google Sheets primarily help you do?

easy

A. Highlight cells based on date conditions

B. Sort dates in ascending order

C. Convert text to dates automatically

D. Calculate the difference between two dates

Solution

1. Step 1: Understand the purpose of date-based formatting

   Date-based formatting changes how cells look based on date rules, like coloring dates before or after today.

2. Step 2: Compare with other options

   Sorting, converting, and calculating dates are different features, not formatting.

3. Final Answer:

   Highlight cells based on date conditions -> Option A

4. Quick Check:

   Date-based formatting = Highlight cells by date [OK]

Hint: Date formatting changes cell look based on dates [OK]

Common Mistakes:

• Confusing formatting with sorting
• Thinking it converts text to dates
• Assuming it calculates date differences

2. Which formula is correct to use in conditional formatting to highlight dates before today?

easy

A. =DATE()

B. =TODAY()

C. =A1

D. =A1>NOW()

Solution

1. Step 1: Identify the correct comparison for dates before today

   To highlight dates before today, the cell date (A1) must be less than TODAY(), so formula is =A1

2. Step 2: Check other options for correctness

   A uses DATE() incorrectly without arguments; B (=TODAY()

3. Final Answer:

   =A1<TODAY() -> Option C

4. Quick Check:

   Use =A1<TODAY() to highlight past dates [OK]

Hint: Use =A1

Common Mistakes:

• Using NOW() instead of TODAY()
• Reversing comparison signs
• Using DATE() without arguments

3. Given the formula =A2>=TODAY() in conditional formatting, what happens if A2 contains the date 2024-06-01 and today is 2024-05-30?

medium

A. The cell is not highlighted because 2024-06-01 is before today

B. The cell is highlighted because 2024-06-01 is after today

C. The formula causes an error

D. The cell is highlighted only if time is included

Solution

1. Step 1: Compare the date in A2 with today's date

   A2 has 2024-06-01, which is after 2024-05-30 (today).

2. Step 2: Evaluate the formula condition

   The formula checks if A2 is greater or equal to TODAY(), which is true, so the cell is highlighted.

3. Final Answer:

   The cell is highlighted because 2024-06-01 is after today -> Option B

4. Quick Check:

   =A2>=TODAY() is TRUE for future dates [OK]

Hint: Dates after or on today make =A2>=TODAY() TRUE [OK]

Common Mistakes:

• Confusing before and after dates
• Thinking formula causes error
• Assuming time affects date comparison here

4. You want to highlight dates that are exactly 7 days from today using conditional formatting. Which formula has an error?

medium

A. =A1=TODAY()+"7 days"

B. =A1=TODAY()-7

C. =A1=TODAY()+7

D. =A1=EDATE(TODAY(),7)

Solution

1. Step 1: Understand how to add days to TODAY()

   Adding 7 as a number to TODAY() works to get a date 7 days ahead.

2. Step 2: Identify the error in =A1=TODAY()+"7 days"

   =A1=TODAY()+"7 days" adds a string "7 days" instead of a number, causing a formula error.

3. Final Answer:

   =A1=TODAY()+"7 days" -> Option A

4. Quick Check:

   Add numbers, not strings, to TODAY() [OK]

Hint: Add numbers, not text, to TODAY() for date math [OK]

Common Mistakes:

• Adding text instead of numbers
• Subtracting instead of adding days
• Using wrong operators

5. You want to highlight all dates in column A that are within the next 3 days including today. Which conditional formatting custom formula should you use?

hard

A. =A1>TODAY()+3

B. =OR(A1>=TODAY(), A1<=TODAY()+3)

C. =A1

D. =AND(A1>=TODAY(), A1<=TODAY()+3)

Solution

1. Step 1: Define the date range condition

   We want dates from today up to 3 days ahead, so A1 must be >= TODAY() and <= TODAY()+3.

2. Step 2: Choose the correct logical function

   AND ensures both conditions are true simultaneously, so use =AND(A1>=TODAY(), A1<=TODAY()+3).

3. Final Answer:

   =AND(A1>=TODAY(), A1<=TODAY()+3) -> Option D

4. Quick Check:

   Use AND for date range between today and 3 days ahead [OK]

Hint: Use AND to check date is between two dates [OK]

Common Mistakes:

• Using OR instead of AND
• Checking only one side of the range
• Using > or < incorrectly