EOMONTH Function – Google Sheets

EOMONTH is a function in Google Sheets typically used to return values in other formulas rather than for use by itself. For the most part, you are already going to know the ending day of most given months. However, if dates are changing in different parts of your spreadsheet, the last day of the month could be changing as well, which would be pretty tricky to nail down without this formula.

Remember when using the EOMONTH function that dates behave differently than numbers, and therefore you cannot type the date directly into the formula. Because of this, you will need to use quotes, the DATE function, or a reference to a cell containing a valid date value.

Video Explanation

YouTube Video about the EOMONTH Function

Purpose

Returns the end date of a month that is a specified number of months away from a starting date

Syntax

=EOMONTH(start_date,number_of_months)

start_date Any day during a starting month. 1/3/2017 and 1/6/2017 would both give the same output.

number_of_months Number of months to go forward or backward

Tip: The value for the number of months will be truncated instead of rounded if you use a decimal. For example, the function will change 3.7 to 3 instead of 4.

Examples

Example 1 – Plain and Simple

Following are a few examples of the EOMONTH function. Notice that the values returned are at the end of each month. On the other hand, if you want to increment by month and stay on the same day of the month, consider using the EDATE Function function.

FormulaDescriptionResult
=EOMONTH(“5/17/2017”,13)Return the last day of the month 13 months after May 17, 20176/30/2018
=EOMONTH(“5/17/2017”,-13)Return the last day of the month 13 months before May 17, 20174/30/2016

The examples show EOMONTH increasing and decreasing the month.

Example 2 – Calculating Employment Benefit Start Dates

Benefits often start after an employee is at a company for a specified number of calendar months. It does not matter when during a month the employee started, only how many whole months have elapsed. The table calculates the day after which the employee becomes eligible for benefits.

 ABC
1Hire DateFormulaDay after which eligibility starts
21/13/2017=EOMONTH(A2,3)4/30/2017
31/30/2017=EOMONTH(A3,3)4/30/2017
412/21/2016=EOMONTH(A4,3)3/31/2017

Even though the three employees started within days of each other, they have different eligibility dates.

Live Examples in Sheets

Go to this spreadsheet for the examples of the EOMONTH function shown above that you can study and use anywhere you would like.

Leave a Comment