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.

Practice

1. What does the Excel function YEAR(A1) do if cell A1 contains the date 2024-06-15?

easy

A. Extracts the month part, returning 6

B. Extracts the year part, returning 2024

C. Extracts the day part, returning 15

D. Returns the full date as text

Solution

1. Step 1: Understand the YEAR function

   The YEAR function extracts the year number from a date value.

2. Step 2: Apply YEAR to the date in A1

   Given the date 2024-06-15, YEAR returns 2024.

3. Final Answer:

   Extracts the year part, returning 2024 -> Option B

4. Quick Check:

   YEAR("2024-06-15") = 2024 [OK]

Hint: YEAR extracts the 4-digit year from a date [OK]

Common Mistakes:

• Confusing YEAR with MONTH or DAY functions
• Expecting a text output instead of a number
• Using YEAR on a non-date value

2. Which of the following is the correct formula to extract the month from a date in cell B2?

easy

A. =DATE(B2)

B. =DAY(B2)

C. =YEAR(B2)

D. =MONTH(B2)

Solution

1. Step 1: Identify the function for month extraction

   The MONTH function extracts the month number from a date.

2. Step 2: Check each option

   =MONTH(B2) uses MONTH(B2), which is correct. Others extract day, year, or are invalid.

3. Final Answer:

   =MONTH(B2) -> Option D

4. Quick Check:

   MONTH(date) extracts month number [OK]

Hint: Use MONTH() to get month number from a date [OK]

Common Mistakes:

• Using DAY or YEAR instead of MONTH
• Using DATE function incorrectly
• Missing equal sign in formula

3. If cell C3 contains the date 2023-12-31, what will the formula =DAY(C3) return?

medium

A. 12

B. Error

C. 31

D. 2023

Solution

1. Step 1: Understand the DAY function

   DAY extracts the day number from a date value.

2. Step 2: Apply DAY to the date 2023-12-31

   The day part of 2023-12-31 is 31.

3. Final Answer:

   31 -> Option C

4. Quick Check:

   DAY("2023-12-31") = 31 [OK]

Hint: DAY() returns the day number from a date [OK]

Common Mistakes:

• Confusing day with month or year
• Expecting a date or text output
• Using DAY on a text string not recognized as date

4. A user wrote the formula =YEAR("2024-07-20") but it returns 1900 instead of 2024. What is the likely problem?

medium

A. The date is entered as text, not a date value

B. YEAR function cannot handle dates in July

C. Formula syntax is incorrect, missing parentheses

D. The cell format is set to text

Solution

1. Step 1: Understand YEAR function input requirements

   YEAR expects a valid date value, not a text string.

2. Step 2: Analyze the formula input

   "2024-07-20" is a text string, so YEAR treats it as zero date, returning 1900.

3. Final Answer:

   The date is entered as text, not a date value -> Option A

4. Quick Check:

   YEAR(text) returns 1900 if text not converted to date [OK]

Hint: Ensure date inputs are real dates, not text strings [OK]

Common Mistakes:

• Using quotes around date in YEAR function
• Assuming YEAR works on text dates
• Ignoring cell format effects

5. You have a date in cell D5 and want to create a formula that returns the date in the format YYYY-MM-DD as text using YEAR, MONTH, and DAY functions. Which formula is correct?

hard

A. =YEAR(D5)&"-"&MONTH(D5)&"-"&DAY(D5)

B. =TEXT(D5, "YYYY-MM-DD")

C. =YEAR(D5)+"-"+MONTH(D5)+"-"+DAY(D5)

D. =CONCAT(YEAR(D5), MONTH(D5), DAY(D5))

Solution

1. Step 1: Understand concatenation with YEAR, MONTH, DAY

   Using & to join numbers and text creates a text string.

2. Step 2: Check each option for correct syntax

   =YEAR(D5)&"-"&MONTH(D5)&"-"&DAY(D5) uses & correctly to join year, month, day with dashes. =TEXT(D5, "YYYY-MM-DD") uses TEXT function (valid but not using YEAR, MONTH, DAY). =YEAR(D5)+"-"+MONTH(D5)+"-"+DAY(D5) uses + which causes error. =CONCAT(YEAR(D5), MONTH(D5), DAY(D5)) misses the dashes between parts.

3. Final Answer:

   =YEAR(D5)&"-"&MONTH(D5)&"-"&DAY(D5) -> Option A

4. Quick Check:

   Use & to join parts into text date [OK]

Hint: Use & to join YEAR, MONTH, DAY with dashes for text date [OK]

Common Mistakes:

• Using + instead of & for text concatenation
• Using TEXT function instead of YEAR, MONTH, DAY
• Not adding dashes between parts