This article explains the syntax and the use of Excel DAY function in Excel.
The Excel DAY function returns the day of the month from a supplied date as an integer ranging from 1 to 31. In other words, using DAY function, you can find out the day of the month that corresponds to a given date.
Suppose, you want to find out the day of the month in which the maximum number of sale happened or which day of the month, you have less workload. DAY is one of the useful functions especially when you want to extract only day component from a given date in Excel. The function can be often seen with other formulas, like DATE, TODAY, EOMONTH, etc.
- serial_number: The only argument is the date of the day you want to find. It can be a reference to a cell, a date returned by using the DATE function, returned by other Excel formulas/functions or supplied directly within double quotes in the same format as system displays.
Tip: Avoid feeding date manually to the argument that can result in returning an unexpected answer when a provided date is in some other format. If the serial_number is text, use DATEVALUE function to convert the text to a date-serial number.
Examples: DAY Function
The following screenshot show some of the examples of DAY function.
=DAY (A2) returns the day of the date in A2 (6-Feb’18) as 6.
In the example above, dates are supplied in a different format, however, still DAY function returns the day value of a date because function ignores the custom formatting and only refer to the actual serial number.
The serial number in cell A5 (43167) is the serial number of the date March 8, 2018, hence it returns 8 as the 8th day of the month.
Get Total Days in a Month
Let’s assume we need to calculate the total number of days from a date. You can use following formula in cell B2.
=DAY (EOMONTH (A2, 0) )
Let’s understand how this formula works. The EOMONTH function returns the last day of the month from a date. So as per above formula, it returns the date value as ’30-Nov-17’ and feeds to DAY function that extracts only the day component from date-value and returns as 30.
Additional Notes: –
- #VALUE! error occurs if the supplied serial_number argument does not return a valid Excel date.
- Excel only handles dates from 1/1/1900.
- Microsoft Excel stores date as a serial number so they can be used easily in calculations.