Cómo utilizar la fórmula DATE en Google Sheets

In spreadsheets, working with dates can be a hassle. Sometimes date formats are not properly recognized, and to use dates in formulas you have to enclose them in quotes. There are plenty of Google Sheets date functions, but the most basic is FECHA.

Quite simply, you enter numbers representing a year, a month and a day and the formula returns these values in a date format. For example, DATE(1985, 12, 25) returns “12/25/1985”.

This seems really basic, so you might be wondering why you’d ever need this function at all. Sometimes looks can be deceiving, and this is one of those times.

Uses of the DATE function

Regional date formatting

Date formats vary from country to country, the two most common formats being “DD/MM/YYYY” and “MM/DD/YYYY”. But there are also other ways to express dates that include words as well as numbers.

DATE standardizes all of this, you have to write the number of the year followed by the number of the month, followed by the number of the day. Even when you use this formula you can still choose how dates are shown in your spreadsheet. Go to Format > Number > More formats > More date and time formats.

Include dates in formulas

Typing DATE into your formula is a quick and reliable way to tell Google Sheets that you are entering a date. An alternative is to use quotes but you must remember to include them correctly and to enter the numbers in the correct order.

Quickly retrieve values from dates in large datasets

You might run across datasets that include separate columns for year, month and day. You can have Google Sheets make a date column, just use the DATE function and merge the values into a single column.

How spreadsheets handle dates

Understanding how spreadsheets manage dates is crucial for effectively working with dates. It is really quite simple: spreadsheets treat dates as whole numbers. Format is then applied to these numbers to make them readable.

This numerical approach is what enables formulas involving dates to calculate durations, deadlines, and schedules accurately. By treating dates as numbers, you can easily compare them, calculate the number of days between two of them, or even add a certain number of days to a given date.

It is not necessary to know exactly how this system works, but it is important how dates are stored. If you ever run into a whole number where a date should be, now you know the reason.

Sintaxis

The syntax for this formula is particularly simple. You just have to feed it three mandatory pieces of information: year, month and day.

• año must be expressed in four digits (e.g. 1976 and not 76).
• mes is the corresponding number for that month (e.g. November is 11).
• day is simply the day number.

You can also include cell references as parameters as in =DATE(A4, B4, C4).

How to use the DATE function in Google Sheets

The image below has a couple of examples of the formula in action.

Note that for the examples in row 2,3 and 4, the outputs are ‘numbers’ while the examples in row 5,6 and 7 are ‘dates’. This is to illustrate that the DATE formula can be used for both.

The example in row 2 has numbers and returns a corresponding value for that date. The parameters for the examples in row 3, 4, 6 and 7 are references to cells that contain year, month and date values. Accordingly, we get the output dates.

The example in row 5 shows that the DATE function in Google Sheets can also use formulas as its parameters.  It still returns a date as expected.

What happens if you enter a value that is not usually expected in a date format? Here I have typed in 16 for the month parameter. Then, in the next example, I have entered 86 for the date parameter. You might expect it to return an error, but surprisingly, it doesn’t. Take a look at the image below:

These results are unexpected. I’ll explain why Google Sheets returned these values.

Because there are only 12 months in a year, Google Sheets carries on counting into the following year. So if you enter 2012 for the year and 13 for the month, Google Sheets counts into the following year. This means that the “13th month of 2012” refers to the first month of 2013: January.

If you look at the first example: DATE(2017, 16, 21) there is no 16th month in a year. Google Sheets counts into the next year, and the result is the 4th month of 2018 (A. The result is April 21 2018.

Similar logic applies to the second example. Again, there is no 16th month so the formula counts into the following year. But there is no 86th day in April. Google Sheets continues counting into the following months: May and June. As a result, DATE(2017,16,86) returns the value June 25 2018. This is because June 25 is 86 days after April began.

Managing dates in Google Sheets

Dealing with dates and dated values in spreadsheets can be a headache. This is just one formula for date but there are plenty more. Get useful tips on how to calculate the difference between dates in Google Sheets y how to use EDATE formula.