Use the DAY, MONTH, YEAR and TEXT functions to extract date components in Excel

Excel allows you to extract specific parts of a date as a serial number using the DAY, MONTH or YEAR functions.

If you want to extract the day or month as text you can use the TEXT function.

Download File

If you would like to follow along download the attachment below.

What are the DAY, MONTH and YEAR functions

These functions each extract the day, month or year as a serial number from a date.

DAY Function: Extracts the day from a date (1-31).
MONTH Function: Extracts the month from a date (1-12).
YEAR Function: Extracts the year from a date (e.g. 2025).

They all follow a simple syntax with just one parameter:

=DAY(serial_number)

=MONTH(serial_number)

=YEAR(serial_number)

For all functions 'serial_number' is the date which you want to extract the day, month, or year.

Using the DAY, MONTH and YEAR functions

Each function only requires one date to be included in the serial_number parameter and then returns the single value as a serial number from the date depending on the function used.

What is the TEXT function

Each of the above functions have returned serial numbers for the date values.

If you want to return the text value for the day or month, either abbreviated as three letters or the full word, you can use the TEXT function to achieve this.

The TEXT function works by converting a value to text, based on a chosen formatting.

Its syntax is:

=TEXT(value, format_text)

The cell reference with the value that you want to format
The specific format you want to have the text output show in

Using TEXT to extract the day as text

The text formats to extract the day from a date as text is:

ddd – Extracts the first three letters of the day
dddd – Extracts the full text of the day NOTE: Only four d’s need to be entered regardless of how many letters the actual day contains

Using TEXT to extract the month as text

The text formats to extract the day from a date as text is:

mmm – Extracts the first three letters of the month
mmmm – Extracts the full text of the month NOTE: Only four m’s need to be entered regardless of how many letters the actual month contains

Conclusion

If you want to extract part of a date as a serial number you can use the DAY, MONTH or YEAR functions.

None of them will extract a text value for any of the days or months, but using the TEXT function you can use ddd or mmm to get the first three letters of the day or month, or use dddd or mmmm to return the full spelling of the day or month regardless of how many letters it is.