Calculate the last day of the month after adding a specified number of months to a date
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
The EOMONTH Function is categorized under Excel Date/Time functions. The function helps to calculate the last day of the month after adding a specified number of months to a date.
As a financial analyst, the EOMONTH function becomes useful when we are calculating maturity dates for accounts payable or accounts receivable that fall on the last day of the month. It also helps in calculating due dates that fall on the last day of the month. In financial analysis, we often analyze revenue generated by an organization. The function helps us do that in some cases.
The EOMONTH function uses the following arguments:
Start_date (required argument) – This is the initial date. We need to enter dates in date format either by using the DATE function or as results of other formulas or functions. For example, use DATE(2017,5,13) for May 13, 2017. This function will return errors if dates are entered as text.
Months (required argument) – This is the number of months before or after start_date. A positive value for months yields a future date; a negative value produces a past date.
How to use the EOMONTH Function in Excel?
To understand the uses of this function, let’s consider a few examples:
Let’s see what results we get when we provide the following data:
In Row 2, the function added 9 to return the last day of December (9+3). In Row 3, the function subtracted 9 to return July 31, 2015. In Row 4, the function just returned January 31, 2017. In Row 5, the function added 12 months and returned the last day in January.
In Row 9, we used the function TODAY so EOMONTH took the date as of today that is November 18, 2017, and added 9 months to it to return August 31, 2018.
Remember that the EOMONTH function will return a serial date value. A serial date is how Excel stores dates internally and it represents the number of days since January 1, 1900.
Example 2 – Using EOMONTH with SUMIF
Assume we are given the sales of different products in the following format:
Now we wish to find out the total revenue that was achieved for the month of January, February, and March. The formula we will use is:
Remember in column E, we must first type 1/1/2017. Then, using a custom format, change it to a custom date format (“mmmm”) to display the month names. If we don’t do it, the formula will not work properly.
Using concatenation with an ampersand (&) is necessary when building criteria that use a logical operator with a numeric value. Hence, we added it to the formula.
Things to remember about the EOMONTH Function
#NUM! error – Occurs if either:
The supplied start_date is not a valid Excel date; or
The supplied start_date plus the value of the ‘months’ argument is not a valid Excel date.
#VALUE error – Occurs if any of the supplied arguments are non-numeric.
If we provide a decimal value for months, the EOMONTH function will only add the integer portion to start_date.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.