ExcelFunctions.net Logo

The Excel DAY Function

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

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 :

DAY( Date )

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.

 Formulas
  A B
1 31-May-2008 =DAY( A1 )
2 21-Mar-1984 =DAY( A2 )
3   =DAY( DATE( 2010, 1, 1 ) )
 Results
  A B
1 31-May-2008 31
2 21-Mar-1984 21
3   1

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

Problem:

The result of your Day function looks like a date (eg. "01/01/1900"), instead of returning an integer.

Solution:

This problem arises because the new cell or column is formatted as a 'date'. In this case, it is only the formatting of the cell that is wrong, NOT the value returned by the function.

To correct this,

  • Highlight the cell(s) with the wrong formatting
  • Right click with the mouse
  • Select the Format Cells ... option and ensure the Number tab is selected
  • Under the Category heading, select the option General and click OK

- See the Excel Formatting page for more details on formatting in Excel.



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:

DAY( Date )

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).






Valid XHTML 1.0 Transitional

Disclaimer Privacy Policy

Copyright © 2008-2010 ExcelFunctions.net