Home » Excel-Built-In-Functions » Excel-Date-And-Time-Functions » Excel-Day-Function Search this site: Custom Search

Related Functions : MONTH Function YEAR Function DATE Function

Basic Description

The Excel DAY function, when supplied with a date, returns an integer representing the day of the month (from 1 - 31).

The format of the function is :

where the Date argument can be supplied to the function as either :

• A references to cells containing dates

or

• A date returned from another formula or function

Warning: If you attempt to input the Date argument as text, there is a chance that Excel may misinterpret this due to different date systems, or date interpretation settings.

Also, although you can input the Date as a serial number, this is not recommended as the serial numbers may vary across different computer systems.

Example

The following example shows the function used to extract the day from 3 different dates. The spreadsheet on the left shows the format of the function and the spreadsheet on the right shows the results for the 3 dates.

Note that, in the above examples, as recommended by Microsoft, the date value is always supplied as either a reference to a cell containing a date, or as a value returned from another function.

Further examples of the Excel Day function can be found on the Microsoft Office website.

Tip:    

If you have a column of dates that you want to categorise by month and year, insert a new column and add the following formula (shown for a date in cell A1):

=A1-DAY(A1)+1

Change the format of the new column to have the date format "mmm-yy".

This formula returns the first day of the month of the original date and will therefore return the same result for all dates that occur in the same month and year. This allows you to easily sort, create pivot tables, etc by month and year.

Common Error With the Excel Day Function

The Day Function In VBA

The Day function is also a built-in function in VBA (used for writing Excel Macros).

Within VBA, the function takes exactly the same format as when used in an Excel spreadsheet:

For example:

Const CurrDate = "29/11/2009" Dim DayNum As Integer DayNum = Day(CurrDate)

After running the above snippet of VBA code, the variable 'DayNum' holds the value 29, which is the day number of the date "29/11/2009" (where the date "29/11/2009" is in the format dd/mm/yyyy).