What Is EOMONTH In Google Sheets?
The End Of Month or the EOMONTH in Google Sheets retrieves the last date of the month of the selected date or the last day of the specified number of months, before or after the given date.
The Google Sheets EOMONTH helps calculate employee benefits w.r.t bonus or increment, project due dates or timeline, number of days or workdays in a month.
For example, we have a date given below and we will find the last day of the month of the date and the last date of 1 month before and after the date, using the Google Sheets EOMONTH function.
Select cell C1, enter the formula =EOMONTH(A2,B2), press “Enter” and drag the formula from C2 to C4 using the fill handle.
The output is shown above. For the same date, we have retrieved the last date of the same month, a month before and after.
[Special Note: In this article, we have used the long date format for better understanding.]
Key Takeaways
- The EOMONTH in Google Sheets is a Date function, as the name suggests, helps us find the end of the month date before or after the selected date.
- It helps us find the last date of any months before or after the given date, except that the month value must be an integer, positive value retrieves the future months last date and the negative values retrieve the past months last date.
- We can use the EOMONTH with the DATE function to find the date and then the last date of the month, DAY function to find the number of days in a particular month or WORKDAY function to calculate the next or the previous nthdate excluding the weekends.
- We can also use EOMONTH along with the IF function to fulfil any given condition or criteria, and retrieve the results.
Syntax
The syntax of the EOMONTH Formula in Google Sheets is,
The mandatory arguments of the EOMONTH Formula in Google Sheets are,
- start_date – It is the date for which the last date must be retrieved according to the months argument value.
- months – It is the month value that lets us know if we need the current months last date or month/months before/after the selected date. It is always a number.
How To Use EOMONTH Function In Google Sheets?
We can use the EOMONTH Function in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets ribbon
Step 1: Choose an empty cell for the output – select the “Insert” tab– click the “Function” option right arrow – click the “Date” option right arrow – select the “EOMONTH” function, as shown below.
Step 2: The “EOMONTH” formulaappears, as shown below. Enter the arguments as cell values or cell references and press “Enter”.
Method #2 – Enter the formula in the worksheet manually
Step 1: Select an empty cell for the output.
Step 2: Type =EOMONTH( in the cell, as shown below. [Alternatively, type =E or =EO and double-click the EOMONTH from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references.
Step 4: Close the brackets and press “Enter”.
Examples
We will consider specific EOMONTH In Google Sheets examples to calculate employee benefits, also use it along with some functions such as DATE(), DAY(), WORKDAY(), etc.
Example #1 – EOMONTH to Calculate Employee Benefits
Consider the dataset given below that consists of five employees’ September months working data. We will use the EOMONTH to Calculate Employee Benefits, whether the employee is eligible for that particular month’s allowance or not.
We have used the EOMONTH in column C for easy access and to combine the result with the IF function.
(The IF function in Google Sheets checks if the selected cell satisfies the given condition and returns logical TRUE or FALSE or the provided alternate expression.)
The steps to calculate employee benefits using the EOMONTH() and IF() are as follows:
Step 1: Select cell D2 and enter the formula =IF(C2-B2<5,”Allowance”,”No Allowance”), as shown below.
Step 2: Press “Enter” and we see that Google Sheets provides the suggested autofill. We can either select this option or drag the formula using the fill handle.
Step 3: Drag the formula from cell D2 to D6 using the fill handle to get the following output.
The output indicates that if the difference between the employee’s worked dates and the end of month date is more than 5, then they are not eligible for monthly allowance.
Example #2 – EOMONTH in combination with DATE function
The dataset consists of split date values. We will use EOMONTH in combination with DATE function.
(The DATE function in Google Sheets combines the split date values in the dataset and returns the date in the valid date format).
The steps to retrieve required results using the EOMONTH() and DATE() are as follows:
Step 1: Select cell D2 and enter the formula =EOMONTH(date(A2,B2,C2),0), as shown below.
Step 2: Press “Enter” and drag the formula from cell D2 to D5 using the fill handle to get the following results.
In the output, the formula first calculates the date using the selected values and returns the date in the valid format. And then, for that date, the end of month date is returned.
Example #3 – EOMONTH in combination with DAY function
The dataset consists of some date values. We will use EOMONTH in combination with DAY function to calculate the number of days in the particular month.
(The DAY function in Google Sheets returns the number of days in the month for the selected date).
The steps to calculate number of days using the EOMONTH() and DAY() are as follows:
Step 1: Select cell B2 and enter the formula =DAY(EOMONTH(A2,0)), as shown below.
Step 2: Press “Enter” and drag the formula from cell B2 to B7 using the fill handle to get the following results.
In the output, the formula first calculates the end of month date for the selected date values, and then, returns the number of days in that particular month.
Example #4 – EOMONTH in combination with WORKDAY function
Consider the dataset given below that consists of random date values. We will use EOMONTH in combination with WORKDAY function to retrieve the next 10th work date excluding the weekends.
(The WORKDAY function in Google Sheets returns the date according to the number of days given, either the date before or after, but it will always exclude the weekends.)
The steps to retrieve the 10th workday using the EOMONTH() and WORKDAY() are as follows:
Step 1: Select cell B2 and enter the formula =WORKDAY(EOMONTH(A2,0),10), as shown below.
Step 2: Press “Enter” and drag the formula from cell B2 to B11 using the fill handle to get the following results.
In the output, the formula first finds the end of month date using the selected values and returns the 10th work day, i.e., excluding the weekends. Therefore, we notice that regardless of the dates of the month the end of the month date is same, and so their respective 10th work dates are retrieved.
Important Things To Note
- If we do not provide one of the argument values, then we will get “#N/A” error.
- Suppose the start_date is invalid or the calculated last day of the month by adding or deducting the specified number of months to or from the given start date is not valid. Then, the EOMONTH() returns #NUM! error.
- If the start_date or months is non-numeric, the EOMONTH() will return #VALUE! error.
Frequently Asked Questions (FAQs)
The EOMONTH in Google Sheets might not work due to the following reasons;
• One or both of the argument values are not provided.
• The 2nd argument values must always be a number. It does not matter whether it is a positive number or a negative number.
• The argument values entered is non-numeric.
• If the dates entered as cell values, cell references or the results of the difference of two dates are not in a valid format, then the formula will not work.
We often forget in which category a function falls, here, the “EOMONTH” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow -select the “EOMONTH” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the EOMONTH in Google Sheets by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “EOMONTH” function, as shown below.
Download Template
This article must help understand EOMONTH in Google Sheets with formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is EOMONTH In Google Sheets. We learn its syntax and how to use it to retrieve the last day of the month with detailed examples. You can learn more from the following articles. –
Leave a Reply