DAY Function

Returns the day number of a date

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:


Example 1

Suppose we are given the following dates, let’s see how the function returns a result.


DAY Function



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:


DAS Function - Example 1


Example 2

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:


DAY Function - Example 2


The formula to use is:


DAY Function - Example 2a


We get the following result:


DAY Function - Example 2b


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)).


Example 3

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 :


DAY Function - Example 3


The formula used is =B5-DAY(B5)+1, as shown below:


DAY Function - Example 3a


We get the following result:


DAY Function - Example 3b


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.


Click here to download the sample Excel file


Additional resources

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:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!