What is Excel DAY Function?
Excel DAY function is a function under Excel Date and Time function. It is commonly used to get the day value from a specified date. The day value returned ranges between 1 and 31.
How Excel DAY Function Works?
Excel DAY function takes the serial number of a date as the input and returns a number between 1 and 31, which represents a day in a month. Dates in Excel are stored in serial numbers before it can be changed to date format as shown in Excel DATE function.
The syntax for Excel DAY function is:
And the Input Argument is as follows:
- serial_number: This is the serial number of the date you specify and want to get the day value. This input argument can be manually entered, or could be a cell reference that contains a date value or the output from a function.
When Excel DAY Function is Used?
Excel DAY function is used when you want to extract the day value from a specified date.
This function is useful for analyst in analysing time-series data. For example, Excel Day function can be used to find out which days has the highest purchase orders or shipments, or which day of the week a restaurant business or retail store is busiest or has the highest number of customers coming in.
- Excel can only handle dates starting from January 1, 1900 (for windows) and 1904 (for Mac).
- Apart from serial numbers, the input argument could also take date entered as:
- text (in double quotes)
- a date stored as text in a cell
- The result of other function.
How to Use Excel DAY Function: Formulas & Examples
Let’s take a look at some examples on how to use Excel DAY function.
Example 1: Use Excel DAY Function with Serial Numbers
Here, Excel DAY function takes the serial numbers of dates as input and provides the day value as seen above.
Often times, the result displayed may look like 1/1/1900 instead of the expected integer. This is because the cell or column is formatted as a “Date”. However, this does not mean that the result returned by the function is wrong.
So in order to correct this, you need to change the format of the cells from “Date” to “General” option.
Example 2: Use Excel DAY Function with Cell Reference
In this example, the DAY function takes the cell reference which contains the date and returns the number that represents the day in the month.
Instead of using the cell reference, the formula could also be written as =DAY(01/01/2015) and you will get the same result.
- Note that, if the date format used is not recognized by Excel, you will get a #VALUE error.
Example 3: Use Excel DAY Function to Get First Day of the Month
So what if you want to change all the days in the date column to the first day of the month?
Yes, this is absolutely possible using Excel DAY function. If you do it manually, you will end up spending precious hours that can be used for something else.
For this, all you need is a simple formula using the DAY function.
The formula to use is =A5-DAY(A5)+1 as shown above and the result returned is a the same specified month and year but with a day that begins on the first (i.e 1).
How does this work?
Remember, Dates in Excel are stored as serial numbers and so you can perform mathematical calculations on the date. So we can subtract the “Day” value from the specified date which will result to zero, and then add 1 to get the day value for the same month and year.
In the above example, using the first date 10/1/2015 which is 10th January 2015. So using the formula as (10/1/2015 – 10) + 1 = 1 and it returns 01/01/2015 which is 1st January 2015.
Also, this same logic can be used to get the last day of a month.
But note that when you use this formula, the result may display a serial number. So just simply change the format of the cells to date format.
Example 4: Use Excel DAY Function to get the Day Value of Current Date
To get the current date, you use the Excel TODAY function. With this, you can easily get the DAY value of the current date by using both DAY and TODAY function in the same formula as shown above.
In the above example, using the formula =TODAY( ) returns 21/12/2021 which is the current date as at the date of writing this tutorial. And when you include the Day function to =DAY(TODAY()), it takes the date value of TODAY as input and returns the day value which is 21.
Common Errors in Excel DAY Function
The following errors may occur in Excel DAY function due to the following reasons:
- #NUM! – This error occurs if the specified argument is a numeric value, but is not one Excel recognizes as a valid date.
- #VALUE! – This occurs if the date specified is a text value and so Excel does not consider it as a valid date.
Other Related Excel Functions
Thank you for learning Excel DAY function at Excel-ur-Data. You can master Excel to upgrade your skills level by learning other Excel functions.
Check out these other useful functions in Excel:
- DAYS Function
- DAYS360 Function
- EDATE Function