Learning to use the various date functions available in Google Sheets certainly takes time, but they can absolutely speed up your productivity when you need to accomplish certain things.
One such function that might come in handy if you’re trying to figure out the last day of a month that falls a certain number of months behind or ahead of a given date is the EOMONTH function.
In this tutorial, I will show you how to use the EOMONTH function in Google Sheets.
The EO month function will return a date that is the last day of a month that is a specified number of months before or after a given date.
The syntax of the EO month function is:
- start_date – this is the starting date to use from which the calculated date will be returned from
- months – this is the number of months before or after the start_date to return. A negative number indicates months before, and a positive number indicates months after. The last calendar day of the month will be returned
Here are some important notes about using the function:
- the start_date parameter has to be a date, a reference to a cell containing a date, or a function that returns a date
- the months parameter of this function has to be an integer. Decimal values will be truncated to remove the decimal. For example, if you use 1.5 as your “months” argument it will be truncated to a 1.
Using the EOMONTH Function
Now that we have gone over the syntax, let’s take a look at how to set this formula up in our spreadsheet.
Here are the steps:
1. In this example, I already have the start_date and months parameters in my spreadsheet. Select the cell where you want the returned date to calculate and type the equals sign (=) on your keyboard. Type “EOMONTH” and press Tab on your keyboard to begin entering into the formula
2. The first parameter of the function is the start date. Input the start date, or select the cell that contains the start date. In my example, this is cell A2. Place a comma when you are done
3. The next argument is the number of months to add or subtract to your start_date. In my example, this is in cell B2. Add a closing parenthesis when you are done
4. Press Enter and your formula will calculate
5. If you have additional dates to calculate you can copy the cell with your original formula and paste it down to the additional cells
As you can see in the example above, the formula will return the last day of the month that “x” number of months away from your start date. If you use a negative number as your “months’ argument, it will subtract months.
If you use a positive number as your “months” argument, it will add months.
Hopefully, you can see now that this function is very easy to use.
You just have to input your start_date and the number of months to add or subtract and you will get your result.
Hopefully, you found this tutorial helpful!