Software & Apps Google Drive Using the DATE Function in Google Sheets Create time-sensitive formulas that update automatically By Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. our editorial process Ted French Updated November 14, 2019 Google Drive Sheets Docs Slides Tweet Share Email 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. These instructions use the web version of Google Sheets and may not be compatible with Microsoft Excel. 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 monthRow 6 converts a text string (Cell A5) into a dateRow 7 displays the day of the week for a given dateRow 8 counts days between current and previous datesRow 9 converts a Julian Day Number (Cell A9) to the current dateRow 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: Click File to open the File menu. Click on Spreadsheet Settings to open the Settings dialog box. Click Locale to see the list of available country settings. Click on your country of choice to make it the current selection. Click Save Settings at the bottom of the dialog box to close it and return to the worksheet. 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.