Properly Enter Dates in Excel with the DATE Function

Use the DATE Function to Enter Dates into Date Formulas

Excel DATE Function
Using the DATE Function in Date Formulas in Excel. Ted French

DATE Function Overview

Excel's DATE function will return a date or the serial number of a date by combining individual day, month and year elements entered as the function's arguments.

For example, if the following DATE function is entered into a worksheet cell,

=DATE(2016,01,01)

the serial number 42370  is returned, which refers to the date January 1, 2016.

Changing Serial Numbers to Dates

When entered on its own - as shown in cell B4 in the image above - the serial number is typically formatted to display the date. The steps needed to accomplish this task are listed below if needed. 

Entering Dates as Dates

When combined with other Excel functions, DATE can be used to produce a wide variety of date formulas as shown in the image above.

One important use for the function - as shown in rows 5 through 10 in the image above - is to ensure that dates are entered and interpreted correctly by some of Excel's other date functions. This is especially true if the entered data is formatted as text.

The DATE Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the DATE function is:

= DATE( Year, Month, Day)

Year - (required) enter the year as a number one to four digits in length or enter the cell reference to the location of the data in the worksheet

Month - (required) enter the month of the year as a positive or negative integer from 1 to 12 (January to December) or enter the cell reference to the location of the data

Day - (required) enter the day of the month as a positive or negative integer from 1 to 31 or enter the cell reference to the location of the data

Notes

  • Excel interprets the Year argument according to the date system your computer is using. By default, Microsoft Excel for Windows uses the 1900 date system.
  • When using this date system, the DATE function will not display dates prior to 1900.
  • If the value entered for the Year argument is between 0 (zero) and 1899, that value is added to 1900 to determine the year. For example, DATE(0,1, 2) returns January 2, 1900 (1900 + 0), while DATE(1899,12,31) returns December 31, 3799 (1900 + 1899). For this reason, the year four digits should be entered for the year to avoid errors - such as "2016" rather than "16".
  • If the Year argument is between 1900 and 9999, that value is entered as the year for the resulting date. For example, DATE(2016,1, 2) returns January 2, 2016.
  • If the Year argument is less than 0 or greater than 9999, Excel returns the #NUM! error value.
  • If the value entered for the Month argument is greater than 12, the value is divided by 12 and the number of full years is added to the Year argument. For example, DATE(2016,15,1) returns the date March 1, 2017 (15 - 12 = 1 year + 3 months).
  • If the Month argument is less than 1, that number of months is subtracted from the current date to give an earlier date. For example, DATE(2016,-5,1) returns the date July 1, 2015.
  • If the value entered for the Day argument is greater than the number of days in the month specified, the function adds that number of days to the next month. For example, DATE(2016,1,35) returns the serial number representing February 4, 2016, which is four days after January 31, 2016.
  • If the Day argument is less than 1, Day subtracts that number of days, from the previous month. For example, DATE(2016,1,-15) returns the serial number representing December 16, 2015, which is 15 days before December 31, 2015.

DATE Function Example

In the image above, the DATE function is used in conjunction with a number of Excel's other functions in a number of date formulas. The formulas listed are intended as a sample of the DATE function's uses.

The formulas listed are intended as a sample of the DATE function's uses. The formula in:

  •  row 5 enters the first day of the current month;
  • row 6 converts a text string (cell A5) into a date;
  • row 7 displays the day of the week for a given date;
  • row 8 counts days between current and previous dates;
  • row 9 converts a Julian Day Number (cell A9) to the current date;
  • row 10 converts the current date (cell A10) to a Julian Day Number.

The information below covers the steps used to enter the DATE function located in cell B4. The output of the function, in this case, shows a composite date created by combining individual date elements located in cells A2 to C2.

Entering the DATE Function

Options for entering the function and its arguments include:

  1. Typing the complete function: =DATE(A2,B2,C2) into cell B4
  2. Selecting the function and its arguments using the DATE function dialog box

Although it is possible to just type the complete function in manually, many people find it easier to use the dialog box which looks after entering the correct syntax for the function.

The steps below cover entering the DATE function in cell B4 in the image above using the function's dialog box.

  1. Click on cell B4 to make it the active cell
  2. Click on the Formulas tab of the ribbon menu
  3. Choose Date & Time from the ribbon to open the function drop down list
  4. Click on DATE in the list to bring up the function's dialog box
  5. Click on the "Year" line in the dialog box
  6. Click on cell A2 to enter the cell reference as the function's Year argument
  7. Click on the "Month" line
  8. Click on cell B2 to enter the cell reference
  9. Click on the "Day" line in the dialog box
  10. Click on cell C2 to enter the cell reference
  11. Click OK to close the dialog box and return to the worksheet
  12. The date 11/15/2015 should appear in cell B4
  13. When you click on cell B4 the complete function =DATE(A2,B2,C2) appears in the formula bar above the worksheet

Note: if the output in cell B4 is incorrect after entering the function, it is possible that the cell is incorrectly formatted. Below are listed steps for changing the date format.

Changing the Date Format in Excel

A quick and easy way to change the format for cells containing the DATE function is to choose one from the list of pre-set formatting options in the Format Cells dialog box. The steps below use the keyboard shortcut combination of Ctrl + 1 (number one) to open the Format Cells dialog box.

To change to a date format:

  1. Highlight the cells in the worksheet that contain or will contain dates
  2. Press the Ctrl + 1 keys to open the Format Cells dialog box
  3. Click on the Number tab in the dialog box
  4. Click on Date in the Category list window (left side of the dialog box)
  5. In the Type window (right side), click on the desired date format
  6. If the selected cells contain data, the Sample box will display a preview of the selected format
  7. Click the OK button to save the format change and close the dialog box

For those who prefer to use the mouse rather than the keyboard, an alternate method for opening the dialog box is to:

  1. Right-click the selected cells to open the context menu
  2. Choose Format Cells… from the menu to open the Format Cells dialog box

###########

If, after changing to a date format for a cell, the cell displays a row of hashtags similar to the example above, it is because the cell is not wide enough to display the formatted data. Widening the cell will correct the problem.

Julian Day Numbers

Julian Day Numbers, as used by a number of government agencies and other organizations, are numbers representing a particular year and day. The length of these numbers varies depending on how many digits are used to represent the year and day components of the number.

For example, in the image above, the Julian Day Number in cell A9 - 2016007 - is seven digits long with the first four digits of the number represent the year and the last three the day of the year. As shown in cell B9, this number represents the seventh day of the year 2016 or January 7, 2016.

Similarly, the number 2010345 represents the 345th day of the year 2010 or December 11, 2010.