How to Use EOMONTH Function in Excel?
EOMONTH Function in Excel: This is a simple yet effective Date related Function in the Microsoft Excel. This function returns the last day of the month after adding or subtracting a specified number of months to a given date. This function helps Managing scheduled payments from Credit Card, to Salaries, and financial calculations, project deadlines, and scheduling.
| Objective | Value Returned by Function |
|---|---|
| Aim to get the last day of the month specified after given months. | EOMONTH Function returns the last day of the month specified after the given months. |
EOMONTH Function – Syntax
=EOMONTH(start_date, months)
Required Arguments
- start_date – The Reference Date
- months – The total number of months to move forward or backward
Function Compatibility
Since Excel 2010, EOMONTH is available as a built-in function. However, EOMONTH Function was first introduced on Excel 2007 as part of the Analysis ToolPak Add-in.
Basic Usage of EOMONTH Function in Excel
| Formula | Result | Description |
|---|---|---|
=EOMONTH("10-Mar-2025", 0) |
31-Mar-2025 | This function will return the last day of the month March 2025 |
=EOMONTH("10-Mar-2025", 1) |
30-Apr-2025 | As 1 is specified in the month parameter, the function will return the last day of the next month from the specified date |
=EOMONTH("10-Mar-2025", -1) |
28-Feb-2025 | This provide the backward (negative) output. As -1 is specified in the function, it returns the last day of the previous month |
=EOMONTH(TODAY(), 0) |
31-Mar-2025 | Instead of specifying the date, we can use TODAY function in the Start_Date to get the output |
=EOMONTH(A1, 3) |
Last Day 3 months the from the date specified in Cell A1 |
The function will check for the data in the Cell A1, and then take the date from that cell, and gives the last day after 3 months from that date in the AI |
Note: While working with DATE functions, you may get the output as random numbers instead of Date as output. This is a very common error that users may encounter, and it can be fixed by changing the output Number Format. We have a detailed article on How to Fix Excel Date Serial Number Formatting Issue. Kindly refer to it.
Using EOMONTH Function with Practical Example & Scenarios
This function has practical benefits in various department like HR Payroll, Loan Dues, Financial Reports, Deadline Calculations, etc. To understand this function in a better way, let us discuss several practical scenarios with examples.
Example 1: Calculating Salary Payment Date
In this scenario, an employee gave his resignation and the month end will be his last date of working, and the salary will be calculated based on that. Instead of checking up the calendar manually, we can automate it using the EOMONTH Function in the Excel.
- The Employee’s notice period starts from March 01, 2025. Hence, to calculate the last day of the month, we can use the following EOMONTH Function.
=EOMONTH("01-Mar-2025", 0)
- The output will be 31st March 2025, as it is the last day of the current month.
Calculating the salary due of an employee to the last day of the specified month
Example 2: Bank Calculating Loan Dues
A Cooperative Bank is issuing a 14 month for a farmer. The loan start on March 10, 2025. Now, the Bank has to calculate the last day of the month after 14 months. They can simply use the EOMONTH Function in the Excel.
=EOMONTH("10-Mar-2025", 14)
- The output will be 31st May 2026. So, May 31st, 2026 is the last day of the month from 14 months from the specified date. The loan gets completed.
Bank Calculating the Loan Due Month using the EOMONTH Function
Example 3: A GPU Company trying to find the last day of the previous Quarter
A GPU Production company wants to review sales data at the end of every quarter. Considering today as March 17, 2025, the company wants to check the last day of the previous quarter that is Q4 of 2024.
- Instead of using the date, let’s use TODAY Function, as today is March 17, 2025.
=EOMONTH(TODAY(), -3)
Note: The Negative sign in the Month Argument informs excel to calculate the previous value (backward value).
- The output will be 31-December-2024. This will be the last day of the previous quarter Q4.
A Company calculating the last day of the previous quarter
Example 4: Finding the Last Friday of a Month (Advanced Usage)
You can easily find the last friday of the given input month using EOMONTH and WEEKDAY function. To do this you might need to use the below formula.
=EOMONTH(B4,0) - MOD(WEEKDAY(EOMONTH(B4,0)),7) - 1
| Input Date | Last Friday | Formula Used to Calculate Last Friday from given date |
|---|---|---|
| 15-Jan-2023 | 27-Jan-2023 | =EOMONTH(B4,0) – MOD(WEEKDAY(EOMONTH(B4,0)),7) – 1 |
| 20-Feb-2023 | 24-Feb-2023 | =EOMONTH(B5,0) – MOD(WEEKDAY(EOMONTH(B5,0)),7) – 1 |
| 25-Mar-2023 | 31-Mar-2023 | =EOMONTH(B6,0) – MOD(WEEKDAY(EOMONTH(B6,0)),7) – 1 |
Simple Formula to return the last friday from the input date values using EOMONTH and nested functions.
Functions Used:
- EOMONTH: This function used to return the last day of the month depending specified number of months before or after it
- MOD: This function is used to return a reminder from the division.
- WEEKDAY: This function is used to return the day of the week as a numerical value.
Other Advanced Examples:
The following examples explains the advanced usage of the EOMONTH function to return output based on given input dates in multiple scenarios.
| Input Date | Final Results | Formula Used for? | Formula Used? |
|---|---|---|---|
| 15 January 2023 | 01 February 2023 | Formula used to Calculate the First Day of Next Month | =EOMONTH(B4, 0) + 1 |
| 20 February 2023 | 8 | Formula used to Calculate the Number of Days Until Month End | =EOMONTH(B5,0) - B5 |
| 25 March 2023 | 03 April 2023 | Formula used to Find the First Monday of Next Month | =EOMONTH(B6,0) + 1 + MOD(8-WEEKDAY(EOMONTH(B6,0)+1,2),7) |
| 14 April 2023 | 31 July 2023 | Formula Used to Calculate a Due Date 3 Months After a Start Date | =EOMONTH(B7,3) |
| 19 August 2023 | 31 August 2023 | Formula used to Find the Last Business Day of the Month | =WORKDAY(EOMONTH(B8,0)+1,-1) |
| 28 December 2023 | 31 January 2024 | Formula to Calculate the Quarter-End Date | =EOMONTH(B9,3-MOD(MONTH(B9)-1,3)) |
Advanced usage of EOMONTH with Nested functions in Excel
Take Away
Just like other essential Date related Functions such as EDATE Function, DATE Function, etc., EOMONTH in the Excel is a useful function to calculate the last day of the month from the specified month. If you have any queries, kindly let us know in the comment section. For more interesting articles, stay tuned to Excel24x7.com
Top comments (0)