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 in spreadsheets.
For example, 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 value.
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.
Contents
Video Tutorial
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)month– The number of the month (February = 2)day– The number of the day (The 13th = 13)
Note that all inputs will be truncated. For example, 10.97 will be truncated to 10.
Errors
#VALUE! – An input is not a number.
#NUM! – The year cannot be less than zero or greater than 10,000.
Related Functions
DATEVALUE – Returns the serial value of a date
TO_DATE – Converts a number to a date.
TIME – Takes three numbers and converts them to a time.
TIMEDIF – Google Sheets Add-On
Calculate any duration in:
- Years
- Months
- Weeks
- Days
- Hours
- Minutes
- Seconds
- Milliseconds
Examples
Example 1 – Plain and Simple DATE Function
First, let’s look at the simple example below.
| 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 and then Date.
Example 2 – Using the DATE Function in Formulas
The DATE function is commonly used in formulas to let Google Sheets know 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.
| 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 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 of the table above. Using quotes or the DATE function lets Sheets see 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 combinations.
| 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 shown above so that you can study and use them anywhere you like.