Date-based formatting in Google Sheets - Dashboard Guide

Dashboard Mode - Date-based formatting

Goal

Show how to highlight dates in a list based on whether they are past, today, or upcoming using color formatting.

Sample Data

Event	Date
Project Start	2024-06-01
Design Review	2024-06-15
Development Complete	2024-06-25
Testing	2024-07-05
Launch	2024-07-15
Post-Launch Review	2024-07-30

Dashboard Components

• KPI Card: Upcoming Events Count
  Formula: =COUNTIF(B2:B7, ">&" & TODAY())
  Shows how many events are scheduled after today.
• Conditional Formatting Rules on Date Column (B2:B7):
  1. Past dates (before today): Format with light gray fill.
  Formula: =B2 < TODAY()
  2. Today's date: Format with green fill.
  Formula: =B2 = TODAY()
  3. Future dates (after today): Format with light blue fill.
  Formula: =B2 > TODAY()
• Table: Shows events and dates with the above formatting applied for easy visual tracking.

Dashboard Layout

+-----------------------+-----------------------+
| Upcoming Events Count  |                       |
|       (KPI Card)       |                       |
+-----------------------+                       |
|                                               |
|               Events Table                      |
|          (with date formatting)                 |
|                                               |
+-----------------------------------------------+

Interactivity

The dashboard updates automatically each day because the formulas use TODAY(). The conditional formatting changes colors based on the current date. The KPI card count updates to show how many events are upcoming from today forward.

Self Check

If you change your computer date to 2024-06-15, which events will be highlighted as today, past, and future? How does the Upcoming Events Count change?

Key Result

Dashboard highlights event dates as past, today, or future with colors and counts upcoming events.