How to Use Excel DAY Function?
The DAY function in Excel returns the day as a number between 1 and 31 from a given date.
DAY Function: A Brief
The DAY function in Excel returns the day of the month as a number between 1 and 31 from a given date. You can use the DAY function to take the day number from a date into a cell. It helps to easily extract the day value from any valid date. The DAY function is useful in situations where you need to work with or display the day portion of a date separately.
| Objective | Value Returned by function |
|---|---|
| Aim to extract the day as a numerical value. | DAY Function will extract & return the day number value from the input date value. |
DAY Function: A Syntax
=DAY(date_value)- The date_value must be a valid Excel Date value. If the value is not a proper Excel date value and then #VALUE! error will be appear in the cell.
Example 1: Extracting the Day from a Date
If you have a date in cell C6 (e.g., 2024-03-12), and you want to extract the day:
=DAY(C6) //Returns 12 as the cell c6 contains date 2024-03-12
Example 2: Sorting data based on day value of date
In the following example, I’m going to use the DAY function to extract the numerical value from the day number and sort the sales based on the day of the month!
- In order to sort the sales data based on day value, create a helper column and extract the day value by using the formula as below
=DAY(C18)- Apply the formula to all the rows in the helper column to extract values for each date.
- We have created a helper column with the title Day of Month and used the DAY function in each cell of this column.
- The results are displayed as shown below.
- Go to the Home tab and use the Sort & Filter option from the Editing panel to sort the helper column.
- After sorting the data from smallest to largest, you will see all the sales data arranged according to the day values of the month in order.
Example 3: Extract Day number from the Today’s date:
You can use the DAY function together with the TODAY function to get the day number from the current date.
Formula used,
=DAY(TODAY())| DAY Function | DAY function in Excel returns the day as a number between 1 and 31 from a given date. |
| TODAY Function | TODAY function is used to display or return the current date. |
Example 4: Extract Day from a Date Created using DATE Function
Here, I’m going to use the DAY function to extract the day number from the date created using the DATE function.
Example 5: Find Day Number from the Last Day of the Month
You can extract the day number from the last day of the given month using EOMONTH Function.
That’s it.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage.
References:
- How To Use the Excel DAY Function (Plus When It’s Helpful) by Indeed, Retrieved on 28/3/25: Link.
- How to properly use the Excel DAY function by StackOverFlow, Retrieved on 28/3/25: Link.
- DAY Excel Formula by Formula bot, Retrieved on 28/3/25: Link.
- How to use DAY function in Excel by Excel Tip, Retrieved on 28/3/25: Link.
- DAY Excel Function by Wallstreet Mojo, Retrieved on 28/3/25: Link.
- DAY Function by Inrule Support, Retrieved on 28/3/25: Link.
