YEAR, MONTH, DAY extraction in Excel - Dashboard Guide

Dashboard Mode - YEAR, MONTH, DAY extraction

Dashboard Goal

Understand how to extract the year, month, and day from dates in Excel to analyze sales data by these time parts.

Sample Data

Order ID	Order Date	Sales
1001	2024-01-15	250
1002	2024-02-20	450
1003	2024-02-25	300
1004	2024-03-10	500
1005	2024-03-15	350
1006	2024-01-30	400
1007	2024-02-05	200

Dashboard Components

• KPI Cards: Show total sales for Year, Month, and Day extracted from Order Date.
• Formulas for Extraction:
  • Year: =YEAR(B2)
  • Month: =MONTH(B2)
  • Day: =DAY(B2)
• Summary Table: Group sales by Year and Month using formulas and SUMIFS.

Example Formulas

• =YEAR(B2) extracts the year from the date in B2 (e.g., 2024).
• =MONTH(B2) extracts the month number (1-12) from B2 (e.g., 1 for January).
• =DAY(B2) extracts the day number (1-31) from B2 (e.g., 15).
• =SUMIFS(C$2:C$8, C$2:C$8, ">0", D$2:D$8, 2, B$2:B$8, ">=01/01/2024", B$2:B$8, "<=12/31/2024") sums sales for February 2024 (note: Excel does not allow YEAR/MONTH inside SUMIFS directly; use helper columns).

Helper Columns

Order Date	Year (C)	Month (D)	Day (E)
2024-01-15	=YEAR(B2)	=MONTH(B2)	=DAY(B2)
2024-02-20	=YEAR(B3)	=MONTH(B3)	=DAY(B3)

Dashboard Layout

+----------------+----------------+----------------+
|   Year KPI     |   Month KPI    |    Day KPI     |
+----------------+----------------+----------------+
|                Sales Summary Table               |
|  Year | Month | Total Sales                        |
|  2024 |   1   | 650                              |
|  2024 |   2   | 950                              |
|  2024 |   3   | 850                              |
+--------------------------------------------------+

Interactivity

Add a filter to select a specific Year or Month. When you choose a year or month, the sales summary table and KPI cards update to show data only for that selection.

Self Check

If you add a filter for Month = 2 (February), which components update?

• The Month KPI card updates to show sales only for February.
• The Sales Summary Table updates to show sales for February grouped by year.
• The Year KPI card remains the same if the year filter is not changed.

Key Result

Dashboard showing how to extract year, month, and day from dates and summarize sales by these parts.