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.

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.

Entering Dates as Dates

When combined with other Excel functions, DATE can be used to produce a wide variety of date formulas.

One important use for the function is to ensure that dates are entered and interpreted correctly by some of Excel's other date functions. This clarification is necessary 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

Usage Notes

Years

Excel interprets the Year argument according to the date system your computer uses. 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 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, always relay years using four digits 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.

Months

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.

Days

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.

Changing the Date Format in Excel

An easy way to change the format for cells containing the DATE function is to choose one from the list of preset formatting options in the Format Cells dialog box. The steps below use the keyboard shortcut combination of Ctrl+1 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 the Number tab in the dialog box
  4. Click Date in the Category list window
  5. In the Type window, click the intended date format
  6. Click the OK button to save the format change and close the dialog box

Alternatively, right-click the selected cells to open the context menu then 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, it is because the cell is not wide enough to display the formatted data. Widening the cell will correct the problem.

Julian Numbers

Julian 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, the Julian Day Number 2016007 is seven digits long with the first four digits of the number representing the year and the last three the day of the year. 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.