Clear Tutorial about EOMONTH Function in Excel with Practical Examples & Scenarios 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. EOMONTH Function in Excel Date related Function Managing scheduled payments 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. 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. Objective Value Returned by Function Objective Objective Objective Value Returned by Function Value Returned by Function 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. Aim to get the last day of the month specified after given months. 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 returns the last day of the month specified after the given months. EOMONTH Function – Syntax =EOMONTH(start_date, months) =EOMONTH(start_date, months) Required Arguments start_date – The Reference Date months – The total number of months to move forward or backward start_date – The Reference Date start_date months – The total number of months to move forward or backward months 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. 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 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 Formula Result Description Formula Formula Result Result Description 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", 0) =EOMONTH("10-Mar-2025", 0) 31-Mar-2025 31-Mar-2025 This function will return the last day of the month March 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) =EOMONTH("10-Mar-2025", 1) 30-Apr-2025 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 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("10-Mar-2025", -1) =EOMONTH("10-Mar-2025", -1) 28-Feb-2025 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 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(TODAY(), 0) =EOMONTH(TODAY(), 0) 31-Mar-2025 31-Mar-2025 Instead of specifying the date, we can use TODAY function in the Start_Date to get the output Instead of specifying the date, we can use TODAY function in the Start_Date to get the output TODAY =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 =EOMONTH(A1, 3) =EOMONTH(A1, 3) Last Day 3 months the from the date specified in Cell A1 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 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. 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. Note 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. 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 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) =EOMONTH("01-Mar-2025", 0) The output will be 31st March 2025, as it is the last day of the current month. The output will be 31st March 2025, as it is the last day of the current 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) =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. 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. 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) Instead of using the date, let’s use TODAY Function, as today is March 17, 2025. =EOMONTH(TODAY(), -3) Instead of using the date, let’s use TODAY Function, as today is March 17, 2025. TODAY =EOMONTH(TODAY(), -3) Note: The Negative sign in the Month Argument informs excel to calculate the previous value (backward value). 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. The output will be 31-December-2024. This will be the last day of the previous quarter Q4. 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 =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 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 Input Date Last Friday Formula Used to Calculate Last Friday from given date Input Date Input Date Last Friday Last Friday Formula Used to Calculate Last Friday from given date 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 15-Jan-2023 15-Jan-2023 27-Jan-2023 27-Jan-2023 =EOMONTH(B4,0) – MOD(WEEKDAY(EOMONTH(B4,0)),7) – 1 =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 20-Feb-2023 20-Feb-2023 24-Feb-2023 24-Feb-2023 =EOMONTH(B5,0) – MOD(WEEKDAY(EOMONTH(B5,0)),7) – 1 =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 25-Mar-2023 25-Mar-2023 31-Mar-2023 31-Mar-2023 =EOMONTH(B6,0) – MOD(WEEKDAY(EOMONTH(B6,0)),7) – 1 =EOMONTH(B6,0) – MOD(WEEKDAY(EOMONTH(B6,0)),7) – 1 Functions Used: 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. EOMONTH: This function used to return the last day of the month depending specified number of months before or after it EOMONTH: MOD: This function is used to return a reminder from the division. MOD: WEEKDAY: This function is used to return the day of the week as a numerical value. WEEKDAY: 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)) 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)) Input Date Final Results Formula Used for? Formula Used? Input Date Input Date Final Results Final Results Formula Used for? Formula Used for? Formula Used? Formula Used? 15 January 2023 01 February 2023 Formula used to Calculate the First Day of Next Month =EOMONTH(B4, 0) + 1 15 January 2023 15 January 2023 01 February 2023 01 February 2023 Formula used to Calculate the First Day of Next Month Formula used to Calculate the First Day of Next Month =EOMONTH(B4, 0) + 1 =EOMONTH(B4, 0) + 1 20 February 2023 8 Formula used to Calculate the Number of Days Until Month End =EOMONTH(B5,0) - B5 20 February 2023 20 February 2023 8 8 Formula used to Calculate the Number of Days Until Month End Formula used to Calculate the Number of Days Until Month End =EOMONTH(B5,0) - B5 =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) 25 March 2023 25 March 2023 03 April 2023 03 April 2023 Formula used to Find the First Monday of Next Month Formula used to Find the First Monday of Next Month =EOMONTH(B6,0) + 1 + MOD(8-WEEKDAY(EOMONTH(B6,0)+1,2),7) =EOMONTH(B6,0) + 1 + MOD(8-WEEKDAY(EOMONTH(B6,0)+1,2),7) WEEKDAY 14 April 2023 31 July 2023 Formula Used to Calculate a Due Date 3 Months After a Start Date =EOMONTH(B7,3) 14 April 2023 14 April 2023 31 July 2023 31 July 2023 Formula Used to Calculate a Due Date 3 Months After a Start Date Formula Used to Calculate a Due Date 3 Months After a Start Date =EOMONTH(B7,3) =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) 19 August 2023 19 August 2023 31 August 2023 31 August 2023 Formula used to Find the Last Business Day of the Month Formula used to Find the Last Business Day of the Month =WORKDAY(EOMONTH(B8,0)+1,-1) =WORKDAY(EOMONTH(B8,0)+1,-1) WORKDAY 28 December 2023 31 January 2024 Formula to Calculate the Quarter-End Date =EOMONTH(B9,3-MOD(MONTH(B9)-1,3)) 28 December 2023 28 December 2023 31 January 2024 31 January 2024 Formula to Calculate the Quarter-End Date Formula to Calculate the Quarter-End Date =EOMONTH(B9,3-MOD(MONTH(B9)-1,3)) =EOMONTH(B9,3-MOD(MONTH(B9)-1,3)) 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. This tutorial is originally published on How to Use EOMONTH Function in Excel? EDATE DATE How to Use EOMONTH Function in Excel
