Conditional formatting with dates in Excel - Dashboard Guide

Dashboard Mode - Conditional formatting with dates

Goal

Highlight upcoming deadlines within the next 7 days to help prioritize tasks.

Sample Data

Task	Deadline	Status
Submit report	2024-06-10	Pending
Team meeting	2024-06-05	Completed
Client call	2024-06-12	Pending
Update website	2024-06-03	Pending
Plan event	2024-06-15	Pending
Review budget	2024-06-08	Completed

Dashboard Components

• KPI Card: Upcoming Deadlines Count
  Formula: =COUNTIFS(B2:B7, ">="&TODAY(), B2:B7, "<="&TODAY()+7)
  Shows how many tasks have deadlines within the next 7 days.
• Table with Conditional Formatting: Task List
  Conditional formatting rule applied to B2:B7:
  Formula: =AND(B2>=TODAY(), B2<=TODAY()+7)
  Cells with deadlines in the next 7 days are highlighted in light orange fill.
• Summary Table: Status Count
  Formula for Pending tasks: =COUNTIF(C2:C7, "Pending")
  Formula for Completed tasks: =COUNTIF(C2:C7, "Completed")
  Shows how many tasks are pending and completed.

Dashboard Layout

+----------------------+-----------------------+
| Upcoming Deadlines   | Status Count          |
| Count (KPI Card)     | (Pending / Completed) |
+----------------------+-----------------------+
|                                              |
|               Task List Table                 |
|                                              |
+----------------------------------------------+

Interactivity

When the current date changes, the conditional formatting and the Upcoming Deadlines Count update automatically to reflect tasks due within the next 7 days.

No manual filters are needed; the formulas use TODAY() to dynamically adjust.

Self Check

If you change the system date to 2024-06-04, which tasks will be highlighted and what will the Upcoming Deadlines Count show?

Answer: Tasks with deadlines from 2024-06-04 to 2024-06-11 will be highlighted. The count will be 3 (Team meeting, Submit report, Review budget).

Key Result

Dashboard highlights tasks with deadlines in the next 7 days and counts upcoming and completed tasks.