Como usar a fórmula EDATE no Planilhas Google

In Google Sheets application, if we need to know the date a specified number of months before or after a given date, the EDATE formula can help us do so.

Sintaxe

EDATE(start_date, months)

• data_inicial – is the date that the EDATE formula calculates the prior or later date separated by the specified number of meses.
• meses – is the number of months that the formula should move either in forward or backward in time, corresponding to a positive value or a negative value respectively.

Usage: EDATE Formula

Let us understand it’s working by making use of a few examples. Please consider the snapshot below. Please note that we have preset the Result column format to “dd-mmm-yyyy”, for the purposes of avoiding ambiguity (given that the date notations varies across countries).

In the first example case, the data_inicial parameter takes in the output that the DATE() function returned. And we have set the meses parameter to ‘4’. Therefore, the formula calculates the result date that is 4 months ahead of 15-Mar-2017.

The second example is pretty similar, except for the data_inicial parameter, we provided a reference to the cell that has a date.

Unlike the previous two cases, the data_inicial for the third case is an absolute number. And also, we have keyed in a negative value for the month’s parameter. The EDATE formula calculates the resulting date by shifting the time from the date value 42846 (equivalent to 21-Apr-2017) back by nine months.

Let us take a look at the fourth case. At the time of writing this post, the date was 21-Apr-2017 (which, the TODAY() function evaluated to in the second example). Factoring a shift of 45 days we added to the TODAY() function, along with a value of -38 for meses parameter, we get the last date of the month as 05-Apr-2014.

Casos de interesse

Vamos experimentar um pouco mais e inserir valores de data baseados em texto para o data_inicialparâmetro.

In the first example, the formula truncates the decimal values and uses only the integer values and accordingly evaluates the resulting date. Of course, it cannot accept negative numbers, as there is no date interpretation for such values.

From the next three examples, we will that the EDATE formula accepts input date strings in the “dd-mmm-yyyy” and “mm-dd-yyyy” formats. But it will throw up an error with “dd-mm-yyyy” date format strings.