Excel EOMONTH Function – How To Use

The EOMONTH function, categorized as a Date/Time function, is a worksheet (WS) function that allows us to find the last date of a month after adding/subtracting 'n' months to/from any given date.

Financial analysts often use the EOMONTH function to calculate maturities for the firm’s accounts payable or accounts receivable.

Syntax

The syntax of the EOMONTH function is as follows:

Arguments:

start_date – This is a value that represents the start date. It must be a valid date, i.e., a serial number that Excel recognizes as a date. If it is entered as text, Excel will return an error.
months – A negative or positive number representing months to be added to or subtracted from the value in the start_date.

Important characteristics of the EOMONTH function

• Inputting a positive number in the months argument produces a future date, and inputting a negative number produces a date in the past.
• If the start_date argument is not a valid serial number that Excel recognizes as a date, the EOMONTH function will return a #NUM! error. It will return a #VALUE! error if the supplied date is non-numeric.
• If the date in the start_date argument has a time element, the EOMONTH function will ignore it.
• EOMONTH returns the last day of the month, which is computed by adding/subtracting n months to/from the initial date. For obtaining a date n months from the initial date, use the EDATE
• The cell containing the EOMONTH function’s output must be formatted as a date since the function will output a serial number.

Examples of EOMONTH Function

Now let's have a look at some of the examples of the EOMONTH function in Excel.

Example 1 – Adding n number of months to EOMONTH function

Let’s start by using the bare-bones formula of the EOMONTH function. We have a date that we want to add 'n' number of months to. Here’s the formula we will use:

Our start_date is December 25, 2001, and we are adding 2 months to it. Note how the output is February 28, 2002, and not February 25, 2002. EOMONTH always returns the last day of the month.

Example 2 – Subtracting n number of months EOMONTH function

The EOMONTH function can also return a date in the past. We’ll use the same formula and same data, except for the months argument, which will now be a negative number, like so:

Our output is September 30, 2001, i.e., the last date for the month, 3 months before our initial date. Again, notice that our output is the last date of the computed month.

Example 3 – Getting the Last Day of the month in start_date

Perhaps, we don’t want to add or subtract any months to our initial date. All we want is the last date of a given month based on our initial date. Quite intuitively, this is the formula we will use:

Here, the EOMONTH just takes our initial date December 25, 2001, adds 0 months to it, and outputs the last day of that month as December 31, 2001.

Example 4 – Getting the Last Day of Current Month Using EOMONTH

We can use the EOMONTH function to get the last day of the current month, too. For this, we’ll nest the TODAY function as the start_date argument in the EOMONTH function, like so:

As of this writing, it is May 2021 and hence the result is May 31, 2021.

Example 5 – Getting First Day of Current Month Using EOMONTH

Let’s think about this logically for a second. While we can’t get the EOMONTH function to directly output the first day of the current month, we can get it to output the last day of the previous month. Adding 1 to this date should give us the first day of the current month. No rocket science, right?

Here’s the formula we will use:

The EOMONTH function by itself will return the last day of the previous month. Subsequently, we just add 1 day (+1) to the output to get the first day of the current month. In our example, the last date of the previous month is April 30, 2021. Adding 1 day returns May 1, 2021.

Using EOMONTH for Dates Stored as Text

Let’s say you have data consisting of dates that are formatted as text. The dates in the text format use a "yyyymmdd" format. You’d like to use this data to compute the end of month date for the month, 3 months after each of these dates.

To accomplish this, we must first convert the text into an Excel-recognized date using the DATE function. We’ll nest the DATE function as start_date argument in the EOMONTH function like so:

The DATE function will convert 20011225 to a valid date, i.e., December 25, 2001, and relay it to the EOMONTH function as the start_date argument. Adding 3 months to this date gives us March 2001 and our final output, of course, is March 31, 2001.

Using EOMONTH with SUMIFS

Let’s get our hands dirty with some interesting formulas now. Let’s say you have data with dates in one column and corresponding revenue in the next column. You’d like Excel to compile this data by month such that you can see the revenue for each month separately.

To do this, we must use the SUMIFS function. Although the formula may look daunting at first, we’ll see that it’s quite simple once we break it down. Here’s the formula we will use:

Let’s divide the SUMIFS formula into three pieces:

• Part 1: (B2:B7)
• Part 2: (A2:A7), “>=” &D4
• Part 3: (A2:A7), “<=” &EOMONTH(D4,0)

Looks much more manageable now, doesn’t it?

Part 1 is just a summation of data in column B, but the output will depend on the criteria defined in parts 2 and 3. Don’t be overwhelmed by the ampersand either—building criteria using logical operators (>, <, etc.) with numeric values requires an ampersand.

The first criterion checks if a date in column A is greater than or equal to the first day of the month in cell D4. For example, we have January 2001 in cell D4. Therefore, Excel will only sum revenue amounts corresponding to those dates that are greater than January 2001.

However, this also includes revenue rows corresponding to February and March. To eliminate these rows, we add another criterion in part 3. Here, we check if a date in column A is less than or equal to the last day of the month in cell D4.

Effectively, parts 2 and 3 narrows down the rows to be included between the first and last day of the month in cell D4. The SUMIFS function will then sum the filtered rows in column B and output the total revenue for that month.

Note that you must manually enter the date in cell D4 as 01/01/2001, otherwise the formula will not work. Once you’ve entered the date, you can custom format the cell as “mmmm” to display just the month.

Using EOMONTH with IF Function

You run a wholesale furniture business—you’re an industrious fella. You supply goods to retailers on a credit policy that requires them to pay within 60 days after the delivery of goods.

You have records that contain the delivery date, retailer’s name, and value of goods delivered. However, you’re starting to get worried about your aging receivables and want to monitor the due dates closely on a monthly basis.

You want the list of retailers and their receivables divided into three categories:

• Retailers whose payments are due for less than 60 days as on month-end,
• Retailers whose payments are due for over 60 days as on month-end, and
• Retailers whose payments are due for over 90 days as on month-end.

To do this, we will use three functions: AND, IF, and EOMONTH.

We will use three separate formulas to filter receivables for each category, as follows:

• Less than 60 days as at the end of the current month (i.e., payment not due by month-end):

• 61-90 days as at the end of the current month (i.e., overdue payments)

• More than 90 days as at the end of the current month (i.e., critically overdue payments)

These formulas may look like Frankenstein, but they are easy to understand if you know how the IF and AND functions work.

We’ll work with the AND function later. Let’s look at how the IF function contributes to this formula.

IF function has three arguments. The first argument is a logical test that produces a Boolean value (i.e., True if the condition is fulfilled, False otherwise). The next argument consists of the value to be output if the result of the logical test is True, and the last argument contains the value to be output if the result is False.

The first and last formulas contain a logical test that checks if the difference between the last day of the current month (using the EOMONTH function) and the date of transaction (cell A2) is less than 60 or more than 91, respectively. If the condition is fulfilled, it will output the amount, or otherwise deliver a blank text string. Easy-peasy, right?

The second formula (i.e., the formula for “overdue payments”) changes just one thing—it adds two logical tests in the IF function’s first argument instead of just one. This makes sense because we need to test if the difference between the last date of the current month and the transaction date is both more than 61 days and less than 90 days.

In order to add two logical tests, we must use the AND function. AND function can have as many arguments (i.e., as many logical tests) as you want. AND argument, too, outputs a Boolean value. It returns True if all logical tests are satisfied, and False otherwise.

Next, the AND function relays this output to the first argument in the IF function. IF function checks if the relayed Boolean value is True or False, and accordingly returns the value in cell A2 (i.e., the value of goods) if the relayed return was True, or a blank text string if the relayed return was False.

These examples should make things airtight so you can fully understand the EOMONTH function and leverage its power in your worksheets. Keep at it and before you champion the EOMONTH function, we’ll have the next function loaded for you and ready to fire away!