The EOMONTH function in Google Sheets stands for “End Of MONTH“, and rightly so. It returns the last calendar date of a month that is a specified number of months behind or ahead of a given date.
Syntax
- start_date – is the date that the EOMONTH function calculates the last date of a month from.
- months – is the number of months that the function should move either in forward or backward in time, corresponding to a positive value or a negative value respectively.
How to use the EOMONTH function
I’ll demonstrate the function using a few examples. Please consider the snapshot below.
Note that the Result column has been formatted to “dd-mmm-yyyy” for the purposes of avoiding ambiguity (given that the date notations varies across countries).
In the first example case, the start_date parameter takes in the output that the DATE() function returned. And I have set the months parameter to ‘0’, meaning that the function will not move the date forward or backwards in time. Therefore, the function evaluates the end date within the month to 31-Mar-2020.
Let’s take a look at the second case. At the time of writing, the date was 14-Nov-2020 (which the TODAY() function evaluated to in the second example). Factoring a shift of 15 days added to the TODAY() function, along with a value of 12 for months parameter, gives the last date of the month as 30-Nov-2021.
The third example is pretty similar, except for the start_date parameter I provided a reference to the cell that has a date.
Unlike the previous three cases, the start_date for the fourth case is an absolute number. I also keyed in a negative value for the months parameter. The EOMONTH function calculates the last date by shifting the time from the date value 42,846 (equivalent to 21-Apr-2017) back by nine months.
Cases of interest
Let us experiment a little further and input text based date values for the start_date parameter.
In the first example, the function trims the decimal values and uses only the integer values. Accordingly, it evaluates the last date of the month. Of course, it cannot accept negative numbers, as there is no date interpretation for such values.
From the next three examples, you can see that the EOMONTH function accepts input date strings in the “dd-mmm-yyyy” and “dd-mm-yyyy” formats. But it will generate an error with “mm-dd-yyyy” date format strings.
Use case: BOMONTH anybody?
Is there a counterpart to EOMONTH, something like BOMONTH (that stands for Beginning Of MONTH)? Apparently, no! But fortunately, you can make use of the EOMONTH function to build a virtual BOMONTH function, as shown in the snapshot below.
In this example, I’ve added a +1 at the end of the function. This adds 1 day, pushing the date from the last day of the month to the first day of the next month. Because this takes the date to the next month, you also have to add -1 as the months parameter. This will shift the function back 1 month, and give you the date of the first day of the current month. The formula here is =EOMONTH(TODAY(),-1)+1.
Use case: last Monday of the month
Since the work week is typically Monday to Friday, it can be helpful in some cases to be able to find the last Monday of every month. In the screenshot below, I’ve done just that.
The WEEKDAY function is used here to get the day of the week that the last day of the month falls on. I’ve used numbers 1 through 7 (1 being Monday, 2 is Tuesday, etc.). By subtracting the weekday and adding 1, you can get the last Monday of the month. The formula for this is =EOMONTH(“5-Dec-2020”,0)-(WEEKDAY(EOMONTH(“5-Dec-2020”,0),2))+1.
If we need to know the date of a specified number of months before or after a given date, we use the EDATE function in Google Sheets.
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.
