Using the DATE Function in Google Sheets

Using the DATE Function in Date Formulas in Google Sheets
Date Formula in Google Sheets. Ted French

Dates and DATE Function Overview

Google Sheets 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,16) -the serial number 42385 is returned, which refers to the date January 16, 2016.

Entering Dates as Dates

When combined with other Google Sheets functions, DATE can be used to produce a wide variety of date formulas. 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 Sheets 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, and 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 - enter the year as a four-digit number (yyyy) or the cell reference to its location in the worksheet.
  • Month - enter the month as a two digit number (mm) or the cell reference to its location in the worksheet.
  • Day - enter the day as a two digit number (dd) or the cell reference to its location in the worksheet.

Errors and Adjustments

The #VALUE! error value is returned by the function if the text data is entered instead of a number. This includes entering a reference to a cell containing text. The #NUM! error value is returned by the function if a five digit number, such as 20016, is entered for the year argument. If there is 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. Another example would be, = DATE(2016,01,32) - which has 32 days for the month of January - adjusts the month argument and returns 2/01/2016. If the 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.

  • 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 into a worksheet include:

  • 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.
  • Using the auto-suggest box to enter the function and its arguments.

Google Sheets 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 opening 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.

If the output in cell B4 is incorrect after entering the function, it is possible that the cell is incorrectly formatted.

Changing the Date Format

  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.

Changing Regional Settings

Like many online apps, Google Sheets 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 Sheets 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 Sheets uses the date December 30, 1899, for a serial number of zero, but unlike Excel, Google Sheets 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 Sheets 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.