The DATE function is simple when considered by itself as it takes three numbers as an input and just returns those numbers as a date. That is all it does. However, this function is useful in building formulas that use the resultant date. Dates require special care when being used in formulas as 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. If you want to perform analysis on this data, the DATE function would turn the three columns into a value that you could then work with.

Further, 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.


Video showing the DATE function

Purpose

The DATE function takes a number for 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

A simple example of the date function.

Formula Description Result
=DATE(1997,2,20) Output February 2, 1997 as a date 2/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 DATE in formulas

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

Formula Description Result
=3/11/2015-3/10/2015 Subtracting 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 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 that 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

Let's look at an example now of calculating age in days 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 as the TODAY() function produced December 27, 2016 when this page was written but the TODAY() function will produce the current day whenever you look at the live spreadsheet.

Tip: The DATEDIF function provides a more powerful method of calculating age. It can be customized to output days, month, years, or a combination thereof.

A B C D E
1 Year Month Day Formula as text Result of formula
2 1996 2 11 =TODAY()-DATE(A2,B2,C2) 7625
3 2011 4 14 =TODAY()-DATE(A3,B3,C3) 2084
4 1974 4 17 =TODAY()-DATE(A4,B4,C4) 15595

 Live examples in Sheets 

Go to this spreadsheet for the examples of the DATE function shown above that you can study and use anywhere you would like.