How to Use the EOMONTH Function in Google Sheets

How to Use the EOMONTH Function in Google Sheets

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.

The 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 EOMONTH function.

 

 

The Anatomy of the EOMONTH Function

The syntax (the way we write) the EOMONTH function is as follows:

=EOMONTH(start_date,number_of_months)

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 EOMONTH function will calculate the last date of a month from.
  • number_of_months is the number of months the EOMONTH function 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 EOMONTH function 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 EOMONTH formula. You will have to use either the quotes, one of the functions that return a date object (DATE, DATEVALUE, 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 N function.
  • 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.

  1. 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.

How to use the EOMONTH function in Google Sheets

 

  1. 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 EOMONTH function 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.

How to use the EOMONTH function in Google Sheets

 

  1. After the opening round bracket, you should add the date that the EOMONTH function 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.

 

  1. Now we will need the number of months the EOMONTH function 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.

How to use the EOMONTH function in Google Sheets

 

  1. If you did everything right, the EOMONTH function will return the date ‘4/30/2020’ which is the last day of a month which falls three months after the hire date.

How to use the EOMONTH function in Google Sheets

 

  1. If you enter the EOMONTH function 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

 

 

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.

How to use the EOMONTH function in Google Sheets

 

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 🙂

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like