What is the DAY Function?
The DAY Function is categorized under Date/Time Function. In financial modeling, we often need to analyze time series data such as revenue. For example, in a restaurant business, we would be interested in knowing which day of the week is the busiest or find out if there is a trend emerging. For a fashion boutique, we would like to see which day of the month with the most number of customers coming in. We would be interested in knowing which days when most purchase orders are placed or shipments are done.
The DAY Function is useful for financial analysts as it returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
Serial_number (required argument) – It is the date of the day we are trying to find. We need to provide the dates to the DAY function as:
- Serial numbers
- Date values returned from other Excel functions
- Reference to cells containing dates
How to use the DAY Function in Excel?
To understand the uses of the DAY function, let’s consider few examples:
Suppose we are given the following dates, let’s see how the function returns a result.
In the example above:
- We provided the dates are supplied as references to cells containing dates or (in the example in cell A2) as a date returned from another Excel function.
- Often, the result you get would look like a date such as 1/1/1900, instead of an integer. It arises due to the cell or column being formatted as a ‘date.’ Remember here that the only issue is the formatting of the cell and not the value returned by the function. To correct this, we need to select the ‘General’ formatting option from the drop-down format menu of the Excel ribbon. It is located in the ‘Number’ group, on the Home tab of the ribbon, as shown below:
Let’s assume we need to add a given number of years to dates. In such scenario this function is useful. The data given is:
The formula to use is:
We get the following result:
Here, in the given formula, the YEAR, MONTH, and DAY functions extract those respective date components. For A2:
= Year 2016
= Month 2 (Feb)
= Date 29th
The DATE function helps in reassembling the component values back into a valid Excel date. In order to add years or to subtract years from the given date, we just need to add the value in B2 to the year component prior to the reassembly, so the formula we used is: =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)).
Suppose we wish to get the first day of the month for a given date. For this, we can form and use a simple formula based on the DAY function.
The data given is :
The formula used is =B5-DAY(B5)+1, as shown below:
We get the following result:
Let’s now understand how this function works. Here, when we used the DAY function, it would return the day value for a date. As shown in the example, the day value for the date in cell A2 (January 25, 1999) is 25. Remember that dates are stored in Excel as serial numbers. As the dates are just serial numbers in Excel, we can subtract 25 and then add 1 to get the date value for January 1, 1999.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources: