Using the DATE Function in Google Sheets

Google Sheets' DATE function will return a date you enter or its serial number by combining individual day, month and year elements entered as the function's arguments.

Entering Dates as Dates

When you combine it with other Google Sheets functions, you can use DATE to produce a wide variety of date formulas. One important use for the function is to ensure that Sheets interprets dates correctly, especially if the entered data isn't in the most useful format.

The DATE function's primary use is to display a date that combines elements such as year, month, or day from different locations in the worksheet, and to ensure that dates used in calculations are number data instead of 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 - 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 function returns a #VALUE! error if it reads text data. The error will also appear if the formula contains a reference to a cell containing text.

The #NUM! error value appears if the year entry is invalid (e.g., if it's a five-digit number). If you enter 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. In both cases, the formula "rolls over" the invalid value by displaying the first month after December 2016 in the first example and the first day after January 2016 in the second.

If you enter the decimal values for an argument, the formula will "round down" to the integer value. For example, the function would interpret "10.25" as "10."

DATE Function Example

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

• 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

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 you're using cell references.
• Using the auto-suggest box to enter the function and its arguments.

Comma Separators

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

Changing Regional Settings

Like many online apps, Google Sheets defaults to the American date format: MM/DD/YYYY. 

If your location uses a different format, you can adjust Google Sheets to display the date in your preferred 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. Click Locale 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 you enter into a worksheet should follow the format of the selected country. You may have to reformat dates you've already entered.

Negative Serial Numbers and Excel Dates

By default, Microsoft Excel for Windows uses a 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, 1800 from January 1, 1850, which results in a value of 18,262 - the number of days between the two dates.

When you enter the same data into Excel, on the other hand, the program automatically converts the date to text data. It also returns a #VALUE! error if you use a "negative" date in a formula.