YEAR, MONTH and DAY in Excel: Split a Date Into Parts

The YEAR, MONTH and DAY functions in Excel split a date into its three parts, each as a plain number. For 15 March 2024, YEAR returns 2024, MONTH returns 3 and DAY returns 15. This guide shows how to use them, how to put a date back together with DATE, and how to fix the odd results that come from blank cells and wrong formats.

What do the YEAR, MONTH and DAY functions do?#

Each function takes one date and returns one part of it. They all have the same simple shape, with a single argument:

The argument is called the serial number, because Excel stores every date as a number that counts days from 1 January 1900. You can point at a cell, use another function like TODAY(), or build a date with DATE. All three functions work in every version of Excel, on Windows and Mac.

For a wider tour of the date toolkit, see the overview of Excel date functions.

How to split a date into year, month and day#

Put each function in its own column and point it at the date. Here is a small orders table:

The formulas in row 2 are =YEAR(B2), =MONTH(B2) and =DAY(B2). Fill them down (Windows: Ctrl+D; Mac: ⌘D).

Splitting dates like this is useful for filtering and grouping. A Year column lets you filter one year in a single click, and a Month column lets you count orders per month with COUNTIF. For counting without helper columns, see how to count dates by month.

How to put a date back together with DATE#

DATE does the opposite job: it takes a year, a month and a day and returns a real date. The syntax is =DATE(year, month, day), always in that order.

Combine DATE with YEAR, MONTH and DAY to change one part of a date and keep the others:

DATE handles overflow for you. A month of 13 becomes January of the next year, and a day of 0 becomes the last day of the previous month. So =DATE(2024,3,0) returns 29 February 2024. There's more on this in getting the first and last day of a month.

DATE is also the fix when your data arrives in three separate columns. If A2 holds 2024, B2 holds 3 and C2 holds 15, =DATE(A2,B2,C2) gives a proper date. If day and month have been read the wrong way round, the guide to fixing swapped days and months uses the same functions.

How to use YEAR and MONTH in other formulas#

Use YEAR and MONTH inside IF, SUMPRODUCT or criteria to work with whole years or months. Some practical examples, using the orders table above with amounts in C2:C5 of 250, 120, 400 and 90:

Label each order with its year and quarter:

=YEAR(B2)&"-Q"&ROUNDUP(MONTH(B2)/3,0)

For 15/03/2024 this returns 2024-Q1: MONTH gives 3, and 3 divided by 3, rounded up, is quarter 1.

Sum the amounts for 2024 only:

=SUMPRODUCT((YEAR(B2:B5)=2024)*C2:C5)

YEAR checks each date, the comparison gives TRUE or FALSE, and multiplying by the amounts keeps only the 2024 rows. The result is 250 + 400 = 650.

Count the days in a month:

=DAY(EOMONTH(B2,0))

EOMONTH(B2,0) returns the last day of B2's month. DAY then reads its day number: 29 for February 2024, because 2024 is a leap year.

If your year starts in a month other than January, plain YEAR isn't enough. The guide to getting the fiscal year from a date shows how to shift it.

Why does YEAR, MONTH or DAY give a strange result?#

Strange results usually mean the input isn't a real date or the output has the wrong format. Here are the common cases:

The blank-cell case is easy to miss. A blank counts as 0, which Excel reads as a date in January 1900. So MONTH of a blank cell returns 1, and a "count by month" formula can quietly add empty rows to January.

To check whether a cell holds a real date, change its format to General. A real date turns into a number like 45366. Text stays as it was.

YEAR, MONTH and DAY vs TEXT#

Use YEAR, MONTH and DAY when you need numbers, and TEXT when you need labels. MONTH returns 3, which you can compare, add to and use in criteria. TEXT(B2,"mmmm") returns the word March, which is text and can't be used in maths.

A good habit: keep the real date in one column, calculate with YEAR, MONTH and DAY, and only turn things into text in the final report column.

Practise it#

Splitting and rebuilding dates is the foundation of almost every date task in a spreadsheet job test. Practise date basics in a live spreadsheet: you'll use DATE, YEAR, MONTH and DAY on real data, and each formula is checked instantly. Or try the exercise on this page first.