Return the year, month or day component of a date

=YEAR(serial_number), =MONTH(serial_number), =DAY(serial_number)

---

Return the year, month or day component of a date using either serial numbers (e.g. 44599 for 7th February 2022) or text values (e.g. "7/2/2022") or cell references (e.g. C3).

• Year is returned as an integer in the range 1900 to 9999.
• Month is returned as an integer in the range 1 to 12.
• Day is returned as an integer in the range 1 to 31.

YEAR, MONTH and DAY syntax requires the serial number representing the date.

---

YEAR, MONTH, DAY with a number.

In the example below, the YEAR, MONTH and DAY functions are used to extract year, month and day components from serial numbers and dates represented by cell references.

---

YEAR, MONTH, DAY with text.

In the example below, the YEAR, MONTH and DAY functions are used to extract year, month and day components from text and dates represented by cell references.

---

Try it now!

• Enter your formulas into the grey cells.

---

Syntax

YEAR(serial_number), MONTH(serial_number), DAY(serial_number)

The YEAR, MONTH and DAY functions consist of 1 argument:

1. serial_number. Required. The serial number representing a date.

---

Scope

YEAR, MONTH and DAY can be applied to numeric or non-numeric values or cell references.

Number:	=YEAR(44599) || result is 2022 where 44599 is the serial number for 7th February 2022
Text:	=MONTH("6-Feb-2022") || result is 2 for February
Cell reference:	=DAY(C11) || result is 7 where the cell reference contains a date containing the day value of 7

Caveats

• Dates should ideally be entered using the DATE function. Working with textual dates is more complicated and may necessitate extracting year, month and day components from a text string using for example LEFT, RIGHT and MID functions.
• Using the YEAR, MONTH or DAY function on a text string that can not be parsed into a date will return a #VALUE! error. Negative serial numbers will return a #VALUE! error.
• The YEAR, MONTH and DAY functions return dates with Gregorian values.
• Excel's date system uses January 1, 1900 as day 1 (serial number =1). Dates before January 1, 1900 need additional calculations.

---

Related Functions

• NOW() returns the current date and time.
• TODAY() returns the current date.

---

Functions Category

• Date & Time

 

---