YEAR, MONTH, DAY Function in Excel: In-Depth

• The DAY function in Excel can return the value of the day of the month as an integer from 1 to 31 (1 is the first date and 31 is the last date in the calendar).
• The MONTH function in Excel can return the value of the month of the year as an integer from 1 to 12 (January to December).
• Meanwhile, the YEAR function in Excel is useful for returning year values from 1900 until 9999

YEAR, MONTH, and DAY formulas in Excel depending on the date and time format. So, you can’t use this formula from references containing data with invalid dates or numbers.

For in-depth understanding, let’s learn more about how to use DAY, MONTH, and YEAR functions in Excel.

YEAR, MONTH, and DAY Function Breakdown

Important! Before continuing, Know that the Valid Date and Time are related to cell formatting. Learn more about Cell Formatting in Excel if you’re not sure.

There are no differences in Excel rules for writing the YEAR, MONTH, and DAY functions (writing rules a.k.a syntax). These 3 formulas all have 1 argument as follows:

Syntax of YEAR, MONTH, and DAY Function in Excel:
=DAY(serial_number)
=MONTH(serial_number)
and
=YEAR(serial_number)

The serial_number argument can be a value manually typed into a formula (a constant), a cell reference, or the result of a formula. The value of this argument must be a valid Date or number.

As I explained at the beginning of this guide, the YEAR, MONTH, and DAY functions will only return the serial number of each argument. Missed it? Read more…

Apart from that, the results of the DAY, MONTH and YEAR formulas are always in the Gregorian calendar.

So even if your date format uses the Hijri Calendar, Excel still produces the equivalent value for the Gregorian calendar.

Note: For in-depth understanding, I divided examples of how to use the YEAR, MONTH, and DAY functions in Excel based on the reference type used in the serial_number argument =DAY(serial_number).

Since the serial_number argument can contain values manually typed into the argument (constants), cell references, or formula results, I split it into 3 examples.

So…

Please download the following Excel file as your practice this time:

#1 – YEAR, MONTH, and DAY Function in Excel for Constants Value

For example, I want to retrieve the day, month, and year values from April 30, 2024.

The date value from “April 30, 2024” can be typed directly into the serial_number argument in the DAY, MONTH, or YEAR formula

You just need to enclose it in double quote (” “) and use the short date format according to your computer system (such as m/d/yyyy or d/m/yyyy).

See the following example:

Note: the DAY formula in cell C1 will take the serial number (between 1 – 31) from the date =DAY("4/30/2024"). The MONTH formula in cell C2 will take the serial number (between 1 – 12) from the date =MONTH("4/30/2024").

The same thing to the YEAR formula, Excel gets the year from the date =DAY("4/30/2024").

That’s the essence of how the DAY, MONTH, and YEAR formulas work in Excel – Retrieving Values/Series Numbers from Dates –

#2 – YEAR, MONTH, and DAY Function in Excel for Cell References

In addition to inputting a date directly into the serial_number argument, you can also reference a cell that contains a date. See the following example:

The difference with the previous example is only in the serial_number argument.

In this example, the day, month, and year values to be retrieved are the date values contained in cell B1.

So you can use the cell reference (B1) as the serial_numer argument.

I think you already understand this. But 1 important thing… – The cell reference must be a valid date –

Important! Excel considers valid date data to be a positive integer where the number 1 is January 1, 1900 (for Windows). I will discuss this in the Sub-Chapter: Date & Time Format in Excel.

Try to prove it by changing the Number Format for cell B1 to general or number as follows:

Note: The decimal value or serial number from April 30, 2024 is 45412.

But…

If you use a cell reference that doesn’t contain a valid date, the YEAR, MONTH, and DAY functions will return the #VALUE! like the following image:

Please see for yourself! You can download the Excel file I talked about above.

#3 – Combined YEAR, MONTH, and DAY Functions with Other Formula

This method only applies if the formula to be used in the serial_number argument returns a valid date.

For example, the TODAY, DATE, and NOW functions are as shown in the following image:

Important! For in-depth understanding, I hope you also learn how to use TODAY, DATE, and NOW Formulas (See Shortcut Guide).

In the example above, I used the following formula:

TODAY	DATE	NOW	Results
=DAY(TODAY())	=DAY(DATE(2024,4,30))	=DAY(NOW())	30
=MONTH(TODAY())	=MONTH(DATE(2024,4,30))	=MONTH(NOW())	4
=YEAR(TODAY())	=YEAR(DATE(2024,4,30))	=YEAR(NOW())	2024

You can see, all the DAY, MONTH, and YEAR functions give the same results, namely Day = 30, Month = 4, and Year = 2024.

This is because the TODAY and NOW functions will return the current date and time according to your computer system.

Note: When I use this formula, my computer system shows the date April 30, 2024.

The =TODAY() function will return the date “4/30/2024”. Then the DAY, MONTH, and YEAR formulas will get the Day, Month, and Year values from the TODAY function.

Likewise with the NOW function.

Meanwhile, the formula =DATE(2024,4,30) will also return the date “4/30/2024” according to the rules for writing this formula.

The results of this formula are also used by the DAY, MONTH, and YEAR functions and return the correct results.

In conclusion, the YEAR, MONTH, and DAY functions in Excel will get the Year, Month, and Day serial numbers (values) from valid dates, whether they are constants or cell references.

However, if you apply it to an invalid date (a combination of text and date), there is always a way around it. You can use a combination of other functions such as TEXT, MID, LEFT, RIGHT, etc. functions.

So, let’s improve your skills in using Excel through the following guide shortcuts: