Entering Dates with the DATE Function in Google Spreadsheets

Prevent Date Errors in Formulas using the DATE Function

Using the DATE Function in Date Formulas in Google Spreadsheets
Using the DATE Function in Date Formulas in Google Spreadsheets. Ted French

Dates and DATE Function Overview

Google Spreadsheet'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,


the serial number 42385 is returned, which refers to the date January 16, 2016.

Changing Serial Numbers to Dates

When entered on its own - as shown in cell D4 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 Google Spreadsheet 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 Google Spreadsheet's other date functions. This is especially true if the entered data is formatted as text.

The DATE function is primarily used:

  • to display a date that combines date elements - such as year, month, or day from different locations in the worksheet
  • to ensure that dates used in calculations are interpreted as dates (number data) instead of text data

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 four digit number (yyyy) or the cell reference to its location in the worksheet

month - (required) enter the month as a two digit number (mm) or the cell reference to its location in the worksheet

day - (required) enter the day as a two digit number (dd) or the cell reference to its location in the worksheet

  • the #VALUE! error value is returned by the function if text data is entered instead of a number - this includes entering a reference to a cell containing text
  • #NUM! error value is returned by the function if a five digit number - such as 20016 - is entered for the year argument
  • if an invalid value for the month or day arguments, the function automatically adjusts the output of the function to the next valid date. For example,
    = DATE(2016,13,1) - which has 13 for the month argument - adjusts the year argument and returns 1/1/2017
    = DATE(2016,01,32) - which has 32 days for the month of January - adjusts the month argument and returns 2/01/2016
  • if decimal values are entered for an argument, the value is truncated to the integer value. For example, the value "10.25" would be interpreted as "10" by the function

DATE Function Example

In the image above, the DATE function is used in conjunction with a number of other functions in a number of date formulas.

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 give date;
  • row 8 counts days between current and previous dates;
  • row 9 converts a Julian Day Number (cell A9) to 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 into a worksheet include:

1) Manually typing in the complete function - just remember that the order must be yyyy, mm, dd such as:

=DATE(2016,01,16) or,

 =DATE(A2,B2,C2) if using cell references

2) Using the auto-suggest box to enter the function and its arguments

Google spreadsheets does not use dialog boxes to enter a function's arguments as can be found in Excel. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

Comma Separators

When using either method to enter the function, note that commas ( , ) are used to separate the function's arguments inside the round brackets.

The steps below cover how to enter the DATE function located in cell B4 in the image above using the auto-suggest box.

  1. Click on cell D4 to make it the active cell - this is where the results of the DATE function will be displayed
  2. Type the equal sign ( = ) followed by the name of the function - date
  3. As you type, the auto-suggest box appears with the names and syntax of functions that begin with the letter D
  4. When DATE appears in the box, click on the name with the mouse pointer to enter the function name and open round bracket into cell D4
  5. Click on cell A2 in the worksheet to enter this cell reference as the year argument
  6. After the cell reference, type a comma ( , ) to act as a separator between the arguments
  7. Click on cell B2 to enter this cell reference as the month argument
  8. After the cell reference, type another comma
  9. Click on cell C2 to enter this cell reference as the day argument
  10. Press the Enter key on the keyboard to enter the closing round bracket " ) " and to complete the function
  11. The date should appear in cell B1 in the format 11/15/2015
  12. When you click on cell B1 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

    To change to a date format in Google Spreadsheets

    1. Highlight the cells in the worksheet that contain or will contain dates
    2. Click on the Format > Number > Date in the menus to change the cell formatting to the date format used by the current regional settings - see below to change the regional settings.

    Changing Regional Settings

    Like many online apps, Google Spreadsheets defaults to the American date format - also known as middle-endian - of MM/DD/YYYY. 

    If your location uses a different date format - such as big-endian (YYYY/MM/DD) or little-endian (DD/MM/YYYY) Google Spreadsheets can be adjusted to display the date in the correct format by adjusting the regional settings.

    To change the regional settings:

    1. Click File  to open the File menu;
    2. Click on Spreadsheet settings...to open the Settings dialog box;
    3. Under Locale in the dialog box, click on the box - default value of United States - to see the list of available country settings;
    4. Click on your country of choice to make it the current selection;
    5. Click Save settings at the bottom of the dialog box to close it and return to the worksheet;
    6. New dates entered into a worksheet should follow the format of the selected country - existing dates may need to be formatted again for the change to take effect.

    Negative Serial Numbers and Excel Dates

    By default, Microsoft Excel for Windows uses a date system that begins in the year 1900.

    Entering a serial number of 0 returns the date: January 0, 1900. In addition, Excel's DATE function will not display dates prior to 1900.

    Google Spreadsheets uses the date December 30, 1899 for a serial number of zero, but unlike Excel, Google Spreadsheets displays dates prior to this by using negative numbers for the serial number.

    For example, the date January 1, 1800 results in a serial number of -36522 in Google Spreadsheets and permits its use in formulas, such as subtracting January 1, 1850 - January 1, 1800 which results in a value of 18, 262 - the number of days between the two dates.

    When the same date is entered into Excel, on the other hand, the program automatically converts the date to text data and returns the #VALUE! error value if the date is used in a formula.

    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.

    Was this page helpful?