Day Formula in excel is used to return the day of the month for the date provided to it as an argument, the range for day for this function is from 1-31 which means the output returned by Day formula is a number, also Day formula can be nested with the other functions of date to find out the day value for the given arguments, given that the syntax for the day function is =Day(Date).
Day Excel Formula (Table of Contents)
- Day Formula in Excel
- Using DAY()
- Using TEXT()
- Nesting TODAY() in DAY()
- Using DAY() for Different Date Formats
- Using DAY() in VBA
Day Formula in Excel
DAY formula in excel is categorized under Date & Time function. DAY() will return the day of the month when we insert a date in the function. Usually, the output of DAY() will be a numeric value which will be ranging from 1 to 31 (i.e. dates of the month). DAY Formula can be used in Excel worksheet functions and VBA (Visual Basic for Applications) as well. We can also extract the day name in the place of values (i.e. 1 to 31) by using TEXT() function along with that.
The Formula for DAY() is as follows
Serial number – It should be a valid Excel date for returning the day of the month.
How to Use Day Formula in Excel?
Following are the examples of DAY Function in Excel.
Example #1 – Simple Example Using DAY()
Assume that we have got project launch dates for different projects as follows:
Now if management wants to know the exact day of the particular month of the launch date we can use DAY Excel Formula to find out the same as follows:
We got the result as 12.
If we drag the formula to other cells, we will get the day of the launch dates for all the projects based on their launch dates like 12, 25, 13, 6, 21.
Example #2 – Get the name of the day instead of just value by Using TEXT()
Assume that we got the joining dates of employees of an organization as follows:
When management wants to group the employees based on the day of their joining then we should use TEXT() along with DAY() to get the day name as per their joining dates.
With this function, we will get the day and we should note to specify the format for the name and make sure it should be same to all the other dates. And the output is as follows:
Drag the Formula from C2 to C12.
We got employees joined on Monday – 4, Tuesday – 1, Wednesday – 1, Thursday – 3, Saturday – 2. By this output with day names, we can group the employees as per the joining days.
Example #3 – Nesting TODAY() in DAY()
Suppose if we want to extract the day of today using DAY Formula, then we should use TODAY() along with DAY(). Let’s see how it works from the below example.
We should take TODAY() inside DAY() because we should pull out the date which would be a valid date for DAY().
TODAY function will give you that particular date and then DAY() will provide the day of that particular date as follows:
As today’s date 29 April 2019 the result from the above operation is “29”.
Example #4 – Using DAY() for Different Date Formats
Let’s take a few different formats of dates that are available in excel which we may use very frequently. The three different formats of dates are as follows:
From the above table we can see three different formats of dates and let’s see how DAY Excel Formula will work on these formats as follows:
- Format – 1: Date along with time is given, but DAY() will extract the day of the date irrespective of time.
- Format – 2: the Date format is in DD-MMM-YY and DAY() will extract the day of the date through the day is in a different place than usual.
- Format – 3: the Date format is number which represents the date – 15th July’2016 and DAY() will display the day of this date by converting the number into a date.
Example #5 – Using DAY() in VBA (Visual Basic for Applications)
In order to execute the DAY Formula in VBA we should first write code in the visual basic editor as follows:
First, we should create the name of the code. In our example, it is Day test() and then provides the date by linking the cell of the date with date1 and the cell would be B4. Now xc should implement with Day() to get the day of the date. Name the result as an answer and then use Day() and link with the date by using date1 which is already linked with the cell that has the date.
Use VBA Msgbox to display the result. In our example, we had saved the result in “answer” so we should give the “answer” in our Msgbox. As we completed the code then we should save it and link the macro with a button in the excel as follows:
Create the button by clicking on the insert tab and then assign the saved macro to the button. Change the name of the button to “Day” as below:
We should give the date in cell B4 because in the macro code we had linked cell “B4” for date i.e. date1 and when we click the “Day” button the message box will pop-out with day number and that would be as follows:
Similarly, we can link different cells which will have the dates and then run the code as per that which will display the day number of the date.
Things to Remember
- DAY Excel formula is used for different purposes and application like getting series of dates by years, getting a day from the date, getting the first day from the month, adding date to specific date and so on.
- DAY() can also be used along with DATE() function for different kind of operations.
- DAY Excel Formula plays an important role in the financial modeling and financial analysis as it returns the day of the date that is represented by a serial number.
- #NUM! – This type of error will be displayed if the argument of DAY() is a numerical value, but not recognized as a valid date.
- #VALUE! – This type of excel error will be displayed if the argument of DAY() is a text value and cannot be considered as a valid date.
- We may encounter some problem with DAY() that the result is not an integer value between 1 and 31 but looks like a date when the cell format is “Date” instead of “General”. We should make sure that the format of the cell or column should be “General”.
- Microsoft Excel can handle dates after 1/1/1990 and DAY() won’t work for the dates before 1/1/1990.
- DAY Formula in Excel also plays an important role in data analysis and logical statements.
You can Download this Day Excel Formula template here – Day Excel Formula Template
This has been a guide to Day Excel Formula. Here we discuss how to use Day Excel Formula to find the day of a particular date with some examples and downloadable excel template. You may learn more about excel from the following articles –