The DAY function returns integer numbers between 1 to 31 as the day of the months. Use the DAY function to extract a day number from a date.
serial_number, required, a valid Excel date in serial number format.
- Excel only handles dates from January 1, 1900, to December 31, 9999. Any date outside this range causes all excel date functions to return a #VALUE! errors.
For example, there is data as shown below. Column A has six dates, and column B has the DAY function with the serial_number argument pointing to column A in the same row.
All DAY functions in column B returns numbers between 1-31, corresponding to the day of the months of each date, except for the last two DAY function.
Why? Let’s check it one by one.
DAY Function #1 – DAY Function #5
Don’t see how it looks in the worksheet to find out the actual date but see the formula bar. The date appears “Monday, November 8, 1920” in cell A2; the appearance depends on the date format for each cell.
Cell A2 to cell A6 has a different date format, but the actual data in the formula bar has the same format mm/dd/yyyy (this format depends on the regional settings of each computer/country).
DAY Function #6
Cell A7 appearance is the same as cell A2, why does the DAY function #6 returns a #VALUE! error? Cell A2 has a valid Excel date, formatted and displays the name of the day, the name of the month, day and year.
Cell A7 contains text, not a valid Excel date. Pay attention to the date at the worksheet and formula bar; both do not match the correct excel date format and causes the #VALUE! error.
DAY Function #7
Cell A8 has a valid Excel date, but the date is outside January 1, 1900, to December 31, 9999 range