Excel provides three different functions to extract a day, month, and year from date. The following is an explanation of each function to extract each value.
Extract Day from Date in Excel
If you want to extract the day from the date, you can use the DAY function. The DAY function requires only one argument, fill it with valid excel date value.
The result, there are four days value and one error #VALUE!. An error occurred because 2/29/2006 is not a valid Excel date value. Why? Because 2006 is not a leap year, so there is no February 29th.
The DAY function result is a number between 1 and 31.
Extract Month from Date in Excel
To extract month from the date you need the MONTH function. Like the DAY function, the MONTH function has only one argument, filled with a valid Excel date value.
There is a #VALUE error. The error appearance is the same place as the #VALUE error in DAY function result. The cause of the error is the same; the date value in cell A5 is not a valid Excel date value. This error will still appear in all excel functions related to the date.
The MONTH function result is a number between 1 and 12.
Extract Year from Date in Excel
To extract the year from date, Excel provides the YEAR function. There is an argument that must be filled with a valid Excel date value.
The results of the DAY and MONTH functions are a number with a narrow range. Instead, the YEAR function is a wide range of numbers between 1900 and 9999.
For years less than 1900 or more than 9999, it will be considered an invalid excel date value. If used by an Excel function (related to the date function) returns a #VALUE! Error.
The DAY, MONTH and YEAR functions extract day, month and year from a date. To do the opposite, converting day, month and year in number to date value, you need the DATE function.