The DATE function is simple when considered by itself. 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 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. 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.
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.
The DATE function takes numbers for month, day and year and returns them as a date.
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)
Example 1 – Plain and Simple
A simple example of the date function.
||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 the year, month and day. This eliminates variances between different countries that write dates in different orders.
||Subtracting two dates by typing them directly into the formula.||-0.00001353485224|
||Subtracting two dates by typing them directly into the formula and by using quotes around them.||1|
||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 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 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.
|1||Year||Month||Day||Formula as text||Result of formula|
Live examples in Sheets
Go to this spreadsheet for the examples of the DATE function shown above, so that you can study and use them anywhere you like.