Excel DAY Function (Examples + Video)
When to Use Excel DAY Function
Excel DAY function can be used when you want to get the day value (ranging between 1 to 31) from a specified date.
What it Returns
It returns a value between 0 and 31 depending on the date used as the input. For example, for the month of February, it will return a day value between 0 and 29.
Syntax
=DAY(serial_number)
Input Arguments
- serial_number: It is the serial number of the date for which you want to get the day value. This could be the output from a function, a cell reference that contains a date value, or could be manually entered.
Additional Notes
- Apart from serial numbers, the DAY function would also work with dates entered as:
- A result of some other function.
- A date entered as text in the DAY function (in double-quotes).
- A date stored as text in a cell.
-
- Excel can only handle dates starting from January 1, 1900 (for windows) and 1904 (for Mac).
Excel DAY Function – Examples
Here are four examples of using the Excel DAY function.
Example 1: Getting the Day value using a serial number
In the example above, the Excel DAY function takes 42736 as the input. 42736 is the serial number of the date January 1, 2017. Since it represents the first day of the month, the DATE function returns 1.
Example 2: Getting the DAY value using Date in a Cell
In the above example, the DAY function takes the cell value from a cell and returns the day number of the month of that specified date. Note that if you use a date format that is not recognized by Excel, it returns a #VALUE! error.
Example 3: Getting the DAY Value of the current date
You can easily get the current day value by using the TODAY function as the input. The TODAY function returns the current date, and the DAY function can use it to return the day value of that month.
In the example above, the TODAY function returns 11-04-2016 (which is the current date while writing this tutorial). DAY function takes TODAY value as the input as returns 11.
Example 4: Getting the First Day of the Month
You can use the Excel DAY function to get the first day of the month of the specified date.
In the example above, the day value for the date in A2 (15-03-2017) is 15. Since dates are stored as serial numbers in Excel, when we subtract the DAY value from this date and add 1 to it, it returns the first day of the month of the specified date.
Note that the dates in D2 and D3 are formatted as dates. It may happen that when you use this formula, you may get a serial number (for example, 42795 for March 1, 2017). You can then simply format it as a date.
The same logic can also be used to find the last day of the previous month. In that case, you can use the following formula (considering that the date is in cell A2):
=A2-DAY(A2)
Excel DAY Function – VIDEO
Other Useful Excel Functions:
- Excel HOUR Function: It can be used when you want to get the HOUR integer value from a specified time value. It returns a value between 0 (12:00 A.M.) and 23 (11:00 P.M.) depending on the time value used as the input
- Excel MINUTE Function: It can be used when you want to get the MINUTE integer value from a specified time value. It returns a value between 0 and 59 depending on the time value used as the input.
- Excel NOW Function: It can be used to get the current date and time value.
- Excel SECOND Function: It can be used want to get the integer value of the seconds from a specified time value. It returns a value between 0 and 59 depending on the time value used as the input.
You May Also Like the Following Excel Tutorials:
