DATE Function – Google Sheets

The DATE function is simple when considered by itself. It takes three numbers as input and returns those numbers as a date. That is all it does. However, this function helps build formulas that use the resultant date. Dates require special care when used in formulas since they don’t behave like regular numbers do in spreadsheets.

You could be working with large tables of demographic data with separate columns for date, month, and year. The DATE function will turn the three columns into one combined value if you analyze this data.

Furthermore, if you want to use a date in a formula instead of referencing a cell with a date value, you need to use the DATE function or surround the date value with quotes. Formulas do not understand dates typed directly into a formula, as shown in the examples below.

YouTube Video Explaining the DATE Function

Purpose

The DATE function takes numbers for the month, day and year and returns them as a date.

Syntax

=DATE(year,month,day)

  • year – The four-digit year (1997 is well, 1997)
  • month – The number of the month (February = 2)
  • day – The number of the day (The 13th = 13, duh)

Examples

Example 1 – Plain and Simple DATE Function

First, let’s look at the simple example below.

FormulaDescriptionResult
=DATE(1997,2,20)Output February 2, 1997, as a date2/20/1997

If you don’t see 2/20/1997 or your international equivalent as the output, change the formatting to date by going to the Format menu and selecting Number.

Example 2 – Using the DATE Function in formulas

The DATE function is commonly used in formulas to let Google Sheets know that you are using a date and its value. There is only one order with DATE to enter the year, month, and day. This order eliminates variances between countries that write dates in different orders.

FormulaDescriptionResult
=3/11/2015-3/10/2015Subtracting two dates by typing them directly into the formula.-0.00001353485224
="3/11/2015"-"3/10/2015"Subtracting two dates by typing them directly into the formula and by using quotes around them.1
=DATE(2015,3,11)-DATE(2015,3,10)Subtracting two dates using the DATE function.1

Sheets doesn’t know you are referring to a date in the first line. Using quotes or the DATE function lets Sheets know that you are using dates.

Example 3 – Calculating Age in Days

Lastly, let’s look at an example of calculating age in days by using the DATE formula. When visiting the live Google Sheet linked at the bottom of this page, you will notice that the results are getting larger. The TODAY() function output December 27, 2016, when I wrote this page, but the TODAY() function will produce the current day whenever you look at the live spreadsheet.

Tip: The DATEDIF function provides a more robust method of calculating age. You can customize the output to days, months, years, or a combination thereof.

 ABCDE
1YearMonthDayFormula as textResult of formula
21996211=TODAY()-DATE(A2,B2,C2)7625
32011414=TODAY()-DATE(A3,B3,C3)2084
41974417=TODAY()-DATE(A4,B4,C4)15595

Live examples in Sheets

Go to this spreadsheet for the examples shown above so that you can study and use them anywhere you like.

Leave a Comment