How to Use Excel DAY Function?

Using DAY Function in Excel 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 of DAY Function to extract day value from a cell having Date value 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! Example of DAY Function to sort sales data by day 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. Sales sorted using DAY Function Go to the Home tab and use the Sort & Filter option from the Editing panel to sort the helper column. Using Sort & Filter option to sort the sales based on the day number 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. Values has been sorted based on the sale of the day number. 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. Using TODAY Function with DAY Function 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. Input Values Output Values Formula Used 2024,5,21 21-May-24 =DATE(2024,5,21) 21-May-24 21 =DAY(B5) Extracting Day number from a date created with 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. Input Date Output Result Formula Used 29-Aug-1995 31 =DAY(EOMONTH(B5,0)) Using DAY Function to extract the last day of the month using EOMONTH Function That’s it. –Narendhiran Vijayakumar. 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.

Apr 9, 2025 - 15:55

Using DAY Function in Excel

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 of DAY Function to extract day value from a cell having Date value

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!

Example of DAY Function to sort sales data by day

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.

Sales sorted using DAY Function

Go to the Home tab and use the Sort & Filter option from the Editing panel to sort the helper column.

Using Sort & Filter option to sort the sales based on the day number

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.

Values has been sorted based on the sale of the day number.

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.

Using TODAY Function with DAY Function

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.

Input Values	Output Values	Formula Used
2024,5,21	21-May-24	`=DATE(2024,5,21)`
21-May-24	21	`=DAY(B5)`

Extracting Day number from a date created with 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.

Input Date	Output Result	Formula Used
29-Aug-1995	31	`=DAY(EOMONTH(B5,0))`

Using DAY Function to extract the last day of the month using EOMONTH Function

That’s it.

–Narendhiran Vijayakumar.

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.