How to Use YEAR, MONTH, DAY, and WEEKDAY in Excel
Introduction
Your order log has full dates, but the monthly summary needs just the month and the staffing report needs the day of the week. YEAR, MONTH, DAY, and WEEKDAY break a date into its parts so you can group, filter, and analyze by any time period.
The Dataset
Here's an order log for a small online store:
| A | B | C | |
|---|---|---|---|
| 3 | Order | Date | Total |
| 4 | 1001 | 10/5/2024 | 240 |
| 5 | 1002 | 10/19/2024 | 85 |
| 6 | 1003 | 11/8/2024 | 190 |
| 7 | 1004 | 1/3/2025 | 310 |
| 8 | 1005 | 1/18/2025 | 125 |
| 9 | 1006 | 2/6/2025 | 275 |
| 10 | 1007 | 3/1/2025 | 95 |
| 11 | 1008 | 3/22/2025 | 170 |
The store needs to break down these orders by time period for a quarterly review.
YEAR
YEAR pulls the four-digit year from a date. The syntax is:
=YEAR(serial_number)
- serial_number: A cell containing a date.
=YEAR(B4)
The result is 2024. For the January order in B7, it returns 2025.
| Order | Date | Year |
|---|---|---|
| 1001 | 10/5/2024 | 2024 |
| 1002 | 10/19/2024 | 2024 |
| 1003 | 11/8/2024 | 2024 |
| 1004 | 1/3/2025 | 2025 |
| 1005 | 1/18/2025 | 2025 |
| 1006 | 2/6/2025 | 2025 |
| 1007 | 3/1/2025 | 2025 |
| 1008 | 3/22/2025 | 2025 |
Three orders in 2024, five in 2025. Pair YEAR with SUMIFS or COUNTIFS and you can total revenue or count orders by year.
MONTH
MONTH returns a number from 1 (January) to 12 (December). The syntax is:
=MONTH(serial_number)
=MONTH(B4)
The result is 10 (October).
| Order | Date | Month |
|---|---|---|
| 1001 | 10/5/2024 | 10 |
| 1002 | 10/19/2024 | 10 |
| 1003 | 11/8/2024 | 11 |
| 1004 | 1/3/2025 | 1 |
| 1005 | 1/18/2025 | 1 |
| 1006 | 2/6/2025 | 2 |
| 1007 | 3/1/2025 | 3 |
| 1008 | 3/22/2025 | 3 |
October, January, and March each have two orders. November and February have one. If the store runs promotions, MONTH is how you measure which ones actually move the needle.
MONTH returns a number, not a name. If you want "October" instead of 10, wrap it in TEXT:
=TEXT(B4, "mmmm")
This returns October. Use "mmm" for the abbreviation (Oct).
Key takeaway: MONTH turns a date into a number you can group by, which makes monthly summaries straightforward.
DAY
DAY returns the day of the month, from 1 to 31:
=DAY(serial_number)
=DAY(B4)
The result is 5 (October 5th). For B8, it returns 18 (January 18th).
DAY is useful when you need to check whether a date falls before or after a cutoff, like a billing cycle that resets on the 15th, or when you're building a date from separate components with the DATE function.
WEEKDAY
WEEKDAY returns a number representing the day of the week. The syntax is:
=WEEKDAY(serial_number, [return_type])
- serial_number: A cell containing a date.
- return_type: Controls which day is 1. The default (1) starts with Sunday = 1. Use 2 to start with Monday = 1.
=WEEKDAY(B4)
The result is 7 (Saturday), since October 5, 2024 falls on a Saturday.
| Order | Date | Weekday | Day |
|---|---|---|---|
| 1001 | 10/5/2024 | 7 | Saturday |
| 1002 | 10/19/2024 | 7 | Saturday |
| 1003 | 11/8/2024 | 6 | Friday |
| 1004 | 1/3/2025 | 6 | Friday |
| 1005 | 1/18/2025 | 7 | Saturday |
| 1006 | 2/6/2025 | 5 | Thursday |
| 1007 | 3/1/2025 | 7 | Saturday |
| 1008 | 3/22/2025 | 7 | Saturday |
Five of the eight orders landed on a Saturday. If you're deciding when to run ads or schedule customer support, that's the kind of pattern worth knowing.
Like MONTH, WEEKDAY returns a number. To get the name, use TEXT:
=TEXT(B4, "dddd")
This returns Saturday. Use "ddd" for the abbreviation (Sat).
Conclusion
YEAR, MONTH, DAY, and WEEKDAY break a date into the pieces you need for grouping and analysis. Once those pieces are in their own column, functions like SUMIFS and COUNTIFS handle the totals. For calculating the difference between two dates or finding business days, see our guide on DATE, DATEDIF, EDATE, and NETWORKDAYS. For conditional totals by month or year, check out our guide on SUMIFS.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.