Date-based formatting in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a sales report where recent sales are highlighted to quickly identify fresh activity.

📊 Data: You have a table with sales records including Sale ID, Product, Sale Date, and Amount.

🎯 Deliverable: Create a sales report where sales made in the last 7 days are highlighted in green.

Sample Data

Sale ID	Product	Sale Date	Amount
101	Notebook	2024-06-10	15.00
102	Pen	2024-06-15	5.00
103	Backpack	2024-06-18	45.00
104	Calculator	2024-06-20	25.00
105	Desk Lamp	2024-06-22	30.00
106	Chair	2024-06-23	85.00
107	Stapler	2024-06-24	10.00
108	Paper	2024-06-25	7.00

1

Step 1: Select the range of Sale Date cells (C2:C9) in your Google Sheets.

No formula needed for selection.

Expected Result

Cells C2 to C9 are selected.

2

Step 2: Open the Conditional Formatting menu from the Format menu.

No formula needed for menu navigation.

Expected Result

Conditional formatting sidebar opens.

3

Step 3: In the Conditional Formatting sidebar, choose 'Custom formula is' under Format rules.

Enter the formula: =AND(C2>=TODAY()-7, C2<=TODAY())

Expected Result

Formula is set to highlight dates from today going back 7 days.

4

Step 4: Set the formatting style to fill the cell with light green color.

Choose fill color: light green.

Expected Result

Cells matching the formula will be highlighted in light green.

5

Step 5: Apply the conditional formatting to the range C2:C9.

Range: C2:C9

Expected Result

Sale dates within the last 7 days from today are highlighted in green.

Final Result

Sale ID | Product   | Sale Date  | Amount
--------|-----------|------------|--------
101     | Notebook  | 2024-06-10 | 15.00 
102     | Pen       | 2024-06-15 | 5.00  
103     | Backpack  | 2024-06-18 | 45.00 
104     | Calculator| 2024-06-20 | 25.00 
105     | Desk Lamp | 2024-06-22 | 30.00 
106     | Chair     | 2024-06-23 | 85.00 
107     | Stapler   | 2024-06-24 | 10.00 
108     | Paper     | 2024-06-25 | 7.00  

*Dates from 2024-06-18 to 2024-06-25 are highlighted in green*

✓Sales from the last 7 days are easy to spot with green highlights.

✓Recent sales include Backpack, Calculator, Desk Lamp, Chair, Stapler, and Paper.

✓This helps the manager quickly focus on fresh sales activity.

Bonus Challenge

Modify the conditional formatting to highlight sales older than 30 days in red.

Show Hint

Use the formula =C2<TODAY()-30 in a new conditional formatting rule with red fill.