Knowing how to use the EOMONTH function in Google Sheets is useful if you want to return a date representing the last day of a month which falls a specified number of months before or after another date.
Table of Contents
EOMONTH stands for ‘End Of Month’. Even though there is no such function as BOMONTH (‘Beginning of Month’), we can use the
EOMONTH function to return a date representing the first day of a month which falls a specified number of months before or after another date. But will come to that later 🙂
If we want to know the date which falls a specified number of months before or after another date, we will use the
EDATE function in Google Sheets. This way, the output will stay on the same day of the month while the month will move forward or backwards depending on the number of months selected.
Now, let’s take an example that will help you understand how to use the
EOMONTH function in Google Sheets.
Say you have a company with three employees who wonder when will they be eligible for a certain benefit.
Usually, benefits start after an employee is at a company for a certain number of months. To know when an employee will be eligible for a certain benefit, we need the day that is a certain number of months after the hire date. But we need only the number of whole months, regardless of the exact day in the month when the employee was hired.
So how do we do that?
Simple. We will use the
EOMONTH function to return a date representing the last day of a month which falls a certain number of months after the hire date. That will be the date after which eligibility starts.
Let’s take a look at the anatomy of the
EOMONTH function, to help you understand how to use it in Google Sheets before learning how to write our own
The Anatomy of the EOMONTH Function
The syntax (the way we write) the
EOMONTH function is as follows:
Let’s break this down to help you understand the syntax of the
EOMONTH function and what each of these terms means:
=the equals sign is the sign we use at the beginning of any function in Google Sheets.
EOMONTH()is our function. We will have to add the date from which to calculate the result and the number of months that shows how many months before or after the start_date to go backwards or forward.
start_dateis the date that the
EOMONTHfunction will calculate the last date of a month from.
number_of_monthsis the number of months the
EOMONTHfunction will move forward (if the number is positive) or backward (if the number is negative) in time.
⚠️ A few notes you should know when writing your own EOMONTH function in Google Sheets:
- The start_date can be any day during the starting month. Whether you put ‘1/5/2020’ or ‘1/15/2020’ as a start_date, the
EOMONTHfunction will return the same result as long as the number_of_months is the same.
- Note that dates behave differently than numbers, so you cannot just type the date directly into the
EOMONTHformula. You will have to use either the quotes, one of the functions that return a date object (
TODAY, or any other), or a reference to a cell containing a valid date value. You can also use a date serial number of the type returned by the
- The value for the number_of_months will have their decimal components truncated instead of rounded. So, ‘2.7’ will be truncated to ‘2’ instead of being rounded to ‘3’, meaning =EOMONTH(A2,2.7) is the same as =EOMONTH(A2,2) and not =EOMONTH(A2,3).
How to Use the EOMONTH Function in Google Sheets
Let’s begin writing our own
EOMONTH function in Google Sheets, step-by-step.
- Once you open your spreadsheet, click on the cell where you want to show your result, to make it active. For this guide, I will click on the cell B2.
- Then, start the function by typing the equals sign ‘=’. After the equals sign, you should start typing the name of the function, which is ‘EOMONTH’. As you type the name of the function, the auto-suggested box with the names of the functions that start with ‘E’ will appear. You can ignore or close it, or you can use it to select the
EOMONTHfunction from the list, so you do not have to type the whole name of the function. Just make sure you choose the right function since sometimes there might be several functions with similar names.
- After the opening round bracket, you should add the date that the
EOMONTHfunction will calculate the last date of a month from. Since we have our start_date in column A, we can use the cell reference instead of typing the date. Enter the A2 and put comma ‘,’ after it, to act as a separator.
- Now we will need the number of months the
EOMONTHfunction will go forward or backwards. In our example, the number of months is ‘3’. Enter the number 3 and the closing round bracket or hit the Enter key to automatically close the function.
- If you did everything right, the
EOMONTHfunction will return the date ‘4/30/2020’ which is the last day of a month which falls three months after the hire date.
- If you enter the
EOMONTHfunction into the cell B3, you will see that the output will be the same even though the hire date is different. Enter =EOMONTH(A3,3) and see for yourself.
How to Use the EOMONTH Function in Google Sheets as the ‘BOMONTH Function’
As it is mentioned at the beginning of this post, there is no such thing as the
BOMONTH function in Google Sheets (BOMONTH = Beginning of Month). However, we can use the
EOMONTH function in Google Sheets to get the first day of a month which falls a specified number of months before or after another date. Let’s take a look at the example below so you can get an idea of how to do it.
Remember our employees? It would be easier if we were able to know the exact day on which the eligibility starts. Fortunately, we can do that with the
EOMONTH function, too.
Click on the cell C2 and enter the same
EOMONTH formula we have used before =EOMONTH(A2,3) just add ‘+1’ after the closing round bracket. You will now get the result ‘5/1/2020’ which is the exact date on which the benefit starts.
That’s it! You did it! You can make a copy of the spreadsheet using the link below and try it for yourself:
You can also use the
EOMONTH function together with the other Google Sheets formulas to create even more effective formulas that will save you time and help sort and filter your data 🙂