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.
- 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.
- Apart from serial numbers, DAY function would also work with dates entered as:
A result from some other function.
- A date entered as text in 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, 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, DATE function returns 1.
Example 2: Getting the DAY value using Date in a Cell
In the above example, 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 current day value by using TODAY function as the input. 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, 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 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):
Excel DAY Function – VIDEO
Related Excel Functions:
You May Also Like the Following Excel Tutorials:
- Creating a holiday calendar in Excel.
- Excel Timesheet Calculator Template.
- Excel Calendar Template.
- How to Automatically Insert Date and Time Stamp in Excel.
- Calculate the Number of Days Between Two Dates.