Excel FormulasYEAR, MONTH and DAY functions in Excel
YEAR, MONTH and DAY return the year, the month number and the day of the month of a date.
A date carries three pieces of information. These three functions pull them apart, so that you can group sales by month, compare years, or find everyone with a birthday this month.
Syntax
=YEAR(serial_number)=MONTH(serial_number)=DAY(serial_number)| Argument | What it is |
|---|---|
serial_number | A date, or a cell that holds one. |
Examples
Each result below is what Excel itself showed for the formula on the sample sheet.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Salesperson | Region | Product | Units | Amount |
| 2 | 05-Jan-2026 | Asha | North | Notebook | 20 | 1200 |
| 3 | 12-Jan-2026 | Ravi | South | Pen | 50 | 500 |
| 4 | 20-Jan-2026 | Meena | North | Bag | 4 | 3000 |
| 5 | 02-Feb-2026 | Asha | East | Pen | 30 | 300 |
| 6 | 14-Feb-2026 | Kabir | South | Notebook | 15 | 900 |
| 7 | 28-Feb-2026 | Ravi | North | Bag | 2 | 1500 |
| 8 | 03-Mar-2026 | Meena | East | Notebook | 40 | 2400 |
| 9 | 15-Mar-2026 | Asha | North | Bag | 3 | 2250 |
| 10 | 22-Mar-2026 | Kabir | South | Pen | 60 | 600 |
| 11 | 01-Apr-2026 | Ravi | East | Notebook | 25 | 1500 |
| 12 | 18-Apr-2026 | Meena | North | Pen | 80 | 800 |
| 13 | 06-May-2026 | Asha | South | Bag | 5 | 3750 |
=YEAR(A2)Result: 2026The year of the date 05-Jan-2026.
=MONTH(A2)Result: 1The month as a number: 1 for January.
=DAY(A2)Result: 5The day of the month.
=TEXT(A2,"mmmm")Result: JanuaryFor the month's name, use TEXT. MONTH gives only the number.
=SUMPRODUCT((MONTH(A2:A13)=1)*F2:F13)Result: 4700The total amount for sales made in January.
=SUMPRODUCT((MONTH(A2:A13)=3)*1)Result: 3How many sales were made in March.
="Q"&ROUNDUP(MONTH(A8)/3,0)Result: Q1The calendar quarter: months 1 to 3 give Q1.
=YEAR(A8)-(MONTH(A8)<4)&"-"&RIGHT(YEAR(A8)+(MONTH(A8)>=4),2)Result: 2025-26The Indian financial year of a date, written as 2025-26. A date in March 2026 belongs to 2025-26.
=DAY(DATE(YEAR(A6),MONTH(A6)+1,0))Result: 28The number of days in the month of the date in A6, February 2026.
How they work
Each takes a date and returns one number.
| Function | Returns | For 05-Jan-2026 |
|---|---|---|
| YEAR | the four-digit year | 2026 |
| MONTH | the month, 1 to 12 | 1 |
| DAY | the day of the month, 1 to 31 | 5 |
The result is an ordinary number. If the cell shows a date in 1900, it has a date format left over. Change the format to General.
For the names of months and days, use TEXT with the codes "mmm", "mmmm", "ddd" or "dddd". For the day of the week as a number, use WEEKDAY.
Grouping by month
The most frequent use is a helper column. Next to each date write =MONTH(A2), then total by that column with SUMIF or a PivotTable.
A warning: the month number alone mixes years together. January 2025 and January 2026 are both 1. When the data covers more than one year, group by year and month. A combined key does it in one column:
=TEXT(A2,"yyyy-mm")gives 2026-01, which also sorts correctly;=YEAR(A2)*100+MONTH(A2)gives the number 202601.
Without a helper column, SUMPRODUCT can test the month inside the formula, as the fifth example shows. SUMIFS cannot, because it does not accept a calculation on its range. With SUMIFS, use a start date and an end date.
Quarters
For the calendar year, =ROUNDUP(MONTH(A2)/3,0) turns months 1 to 12 into quarters 1 to 4.
For the Indian financial year, April to March, shift the month first: =MOD(MONTH(A2)-4,12) is 0 for April and 11 for March, so the quarter is =INT(MOD(MONTH(A2)-4,12)/3)+1. April to June gives 1, and January to March gives 4.
The financial year
A date from April onwards belongs to the financial year that starts in its own calendar year. A date from January to March belongs to the one that started the year before. The start year is therefore =YEAR(A2)-(MONTH(A2)<4). The second-last example builds the usual label, such as 2025-26, from that.
Birthdays and anniversaries
To find the rows whose day and month match today, compare both parts: =AND(MONTH(B2)=MONTH(TODAY()),DAY(B2)=DAY(TODAY())). For "birthdays this month", the MONTH test alone is enough.
To sort a list of birthdays through the year, ignoring the year of birth, sort by a helper column of =TEXT(B2,"mm-dd").
Rebuilding a date
These functions are the partners of DATE. Take a date apart, change a part, and put it together again: =DATE(YEAR(A2),MONTH(A2),1) is the first day of the same month.
Common mistakes
#VALUE!when the "date" is really text. A date typed in a form Excel did not recognise stays as text, at the left of the cell. Convert it first.- An empty cell counts as day 0, which Excel shows as January 1900. So MONTH of an empty cell is 1, and blank rows get counted as January. Guard with IF:
=IF(A2="","",MONTH(A2)). - Mixing years by grouping on the month number alone.
- A result that looks like a date because of leftover formatting.
In Google Sheets
YEAR, MONTH and DAY are the same in Google Sheets.
Related functions
Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.
This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.