How to Use the EDATE Function in Google Sheets

May 2024 ยท 3 minute read

Date and time functions make up a big portion of Google Sheets' arsenal of functions, and one of these functions is EDATE. This function lets you add or subtract months to a date or a date value. EDATE is a simple function, but has the potential to make sophisticated spreadsheets come to life when combined with other functions.

Read on to learn what EDATE is and then see it in action with a simple example.

What Is the EDATE Function in Google Sheets?

EDATE is a date function in Google Sheets that adds or subtracts the specified amount of months to the date and then displays the final date. This function is most practical when used in combination with other date functions in Google Sheets. EDATE's syntax is as below:

=EDATE(starting_date, months)

You can enter a number (date value), a date, or a cell containing either of these as the starting date and then the number of months to add to or subtract from the starting date. If the months value is positive, EDATE will add that many months to the starting date, and if it's negative, EDATE will subtract that many months from the starting date.

Google Sheets stores dates in a number format known as date value. The date value begins from December 30th, as the zero point. This makes January 1st, 1900, day 1 in this format. The date value starts from negative infinity and goes all the way to positive infinity. For instance, the date value for 1/29/2022 would be 44590, whereas the date value for 2/14/1784 would be -42322.

You can use functions to convert date to date value, and vice versa.

How to Use the EDATE Function in Google Sheets

The EDATE function in Google Sheets needs a date, or a date value to operate on. When you enter a date such as 1/29/2022 in a cell, Sheets automatically converts it to a date format so that it's usable for date-related functions such as EDATE. You can also use a number to indicate a date.

In this example, we have the date value of a certain day, and want to add four months to it and see the results in the date format. To achieve this, we'll make use of the EDATE function.

  • Select the cell where you want to display the final date. That will be cell C2 for this example.
  • In the formula bar, enter the formula below:
    =EDATE(A2, B2)
    The formula summons the EDATE function, and instructs it to add the date in cell A2 by the months specified in cell B2.
  • Press Enter. EDATE will now show the new date.

    • Though straightforward to use, EDATE can only perform on positive date values. This means that EDATE can't work with dates prior to 12/30/1899 as their date value is lesser than 0.

    Back to the Future With EDATE

    EDATE is a simple date function that lets you add or subtract months from a date. The value EDATE returns is in date format, which is usable by other date-related functions in Google Sheets.

    Now that you know what EDATE is and how you can use it, you can combine it with other functions to create complex formulas.

    ncG1vNJzZmivp6x7rq3KnqysnZ%2Bbe6S7zGifqK9dqbxuwdKeZJ6ckamybrLUp5qtoZ%2Bjeqi7zqCjnmWjnbKmwNJo