The DAY function is one of the date and time functions in Excel. It returns the day of the month as a number from 1 to 31, given a date value. For example, if the date is 14/02/2024, the DAY function will return 14.
Syntax and Arguments
The syntax of the DAY function is:
=DAY(date)
The date argument can be:
- A date value entered directly, such as
=DAY("14/02/2024") - A reference to a cell that contains a date, such as
=DAY(A1) - A result of another formula or function that returns a date, such as
=DAY(TODAY())
How to Use the DAY Function
To use the DAY function, follow these steps:
- Select a cell where you want to enter the formula.
- Type
=DAY(and then select or type the date argument. - Press Enter or Tab to complete the formula.
The result will be the day of the month as a number from 1 to 31.
Example: Calculating the Number of Days Left in the Month
One of the applications of the DAY function is to calculate the number of days left in the month. To do this, we need to use the EOMONTH function, which returns the last day of the month for a given date. Then, we can subtract the day of the date from the day of the last day of the month.
For example, suppose we have a list of dates in column A, and we want to calculate the number of days left in the month for each date in column B. We can use the following formula in column B:
=DAY(EOMONTH(A2,0))-DAY(A2)
The EOMONTH(A2,0) part returns the last day of the month for the date in A2. The DAY(A2) part returns the day of the month for the date in A2. The subtraction gives us the number of days left in the month.
Here is a sample table with the formula and the results:
| Date | Days Left |
|---|---|
| 14/02/2024 | 14 |
| 15/03/2024 | 16 |
| 20/04/2024 | 10 |
| 25/05/2024 | 6 |
| 30/06/2024 | 0 |
Other Approaches
There are other ways to extract the day part of a date in Excel, such as:
- Using the TEXT function, which converts a date to a text string in a specified format. For example,
=TEXT(A2,"d")will return the day of the month as a text string. - Using the DATEVALUE function, which converts a text string to a date value. For example,
=DAY(DATEVALUE("14/02/2024"))will return 14. - Using the DATE function, which returns a date value given the year, month, and day arguments. For example,
=DAY(DATE(2024,2,14))will return 14.
However, the DAY function is the simplest and most direct way to get the day of the month as a number from a date value.
