YEAR, MONTH and DAY in Excel: how to use it
YEAR, MONTH and DAY take the year, the month or the day from a date. With WEEKDAY and ISOWEEKNUM you find the day of the week and the week number.
Try the first exercises for freeIn the lesson YEAR, MONTH, DAY and WEEKDAY
What do YEAR, MONTH and DAY do?
YEAR, MONTH and DAY each take one part out of a date: the year, the number of the month or the number of the day. Each time you get an ordinary number back, not a date.
With WEEKDAY you find the day of the week, and with ISOWEEKNUM the week number according to the international standard, in which weeks start on a Monday. That's how you group data by month or by week, or see straight away which dates fall at the weekend.
When do you use YEAR, MONTH and DAY?
- The year of each invoice, to count per year: =YEAR(A2).
- Adding up all the sales from March: =SUMPRODUCT((MONTH(A2:A100)=3)*B2:B100).
- Checking whether a date falls at the weekend: =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday").
- The week number for a schedule: =ISOWEEKNUM(A2).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The year.
Filled in: =YEAR(A2)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
YEAR | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | YEAR |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
date | A cell with a date, or a formula that gives a date. | A2 |
YEAR, MONTH and DAY step by step
C1 contains =MONTH(A2). 14 February falls in month 2. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type MONTH with a bracket.
=MONTH( - Click the cell with the date, A2.
=MONTH(A2 - Close the bracket and press Enter: February is month 2. If you see a date instead of 2, set the cell to General.
=MONTH(A2)
More examples
The day of the week
C1 contains =WEEKDAY(A2,2). 14 February 2026 is a Saturday. With type 2, Monday is 1, so Saturday is 6.
Weekend or weekday
C1 contains =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday"). Saturday and Sunday have the numbers 6 and 7, so everything above 5 is the weekend.
The week number
C1 contains =ISOWEEKNUM(A2). 14 February 2026 falls in week 7 according to the international standard.
The day of the week
=WEEKDAY(A2,2) gives the day of the week with Monday as 1 and Sunday as 7. Without the 2, Excel starts on Sunday, which often confuses people.
The week number
=ISOWEEKNUM(A2) gives the ISO week number: week 1 is the week with the first Thursday of the year. WEEKNUM counts differently and sometimes gives another number around New Year.
Where it often goes wrong
- If you see a date in January 1900 after =MONTH(A2), the cell still has a date format. Set it to General.
- A date stored as text in the cell gives #VALUE!.
Practise this in the lesson YEAR, MONTH, DAY and WEEKDAY. The first exercises are free.
Frequently asked questions
How do I get the name of the month?
With TEXT: =TEXT(A2,"mmmm") gives February.