Certainly, while working in Excel, we often have dates in the dataset and luckily, Excel has a palette of functions for dealing with dates in the dataset. In this tutorial, we’ll learn about how to use the **EOMONTH** function in Excel and combine it with other functions. Moreover, we’ll also explore its quirks and features.

In this scenario, the above screenshot is an overview of the article, which represents the application of the Excel EOMONTH function. In the following sections, you’ll learn more about the dataset as well as how to use the function.

**Table of Contents**hide

## Excel EOMONTH Function: Syntax & Arguments

**Function Objective:**

Simply put, the **EOMONTH **function returns a string of numbers that represent the last day of the month before or after a specified number of months.

**Syntax:**

`=EOMONTH(start_date, months)`

**Argument Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

start_date |
Required | The starting date. |

months |
Required | The number of months prior to or after the starting date. |

**Return Parameter:**

The last day of the month in the past or in the future of the specified month.

**Version:**

For one thing, the **EOMONTH **function was introduced in **Excel 2007** and is available in all versions after that.

## How to Use EOMONTH Function in Excel: 10 Ideal Examples

First and foremost, let’s consider the **List of Product and Delivery Dates **dataset shown in the **B4:C13** cells containing the *“ Product” *and

*“*columns. Here, we want to determine the last days of each month and combine the Excel

**Delivery Date**”**EOMONTH**function with other functions to derive valuable insights from our dataset. Henceforth, without further delay, let’s see each example in detail and with the necessary illustrations.

Here, we have used the *Microsoft Excel 365* version; you may use any other version according to your convenience.

### Example 1: Basic Usage of EOMONTH Function

In the first place, let’s start off with the basic usage of the **EOMONTH** function and move to more complex examples thereupon.

📌 ** Steps**:

- Initially, go to the
**D5**cell >> type in the**EOMONTH**function and enter the two arguments.

`=EOMONTH(C5,0)`

Here, the **C5** cell refers to the *“ Delivery Date”* while the

**0**(

**Zero**) indicates the present month.

### Example 2: Finding First and Last Days of the Current Month

Besides, we can find the first and last days of the current month using Excel’s **EOMONTH **function.

📌 ** Steps**:

- Initially, move to the
**C4**cell >> apply**the TODAY function**to return today’s date.

`=TODAY()`

- Next, jump to the
**C6**cell >> use the formula given below.

`=EOMONTH(TODAY(),-1)+1`

In this case, the **-1** value prompts the **EOMONTH** function to yield the last day of the previous month. Afterward, adding **1** to the answer gives the first date of the current month.

- Then, insert the expression below into the
**C7**cell to retrieve the last of the current month.

`=EOMONTH(TODAY(),0)`

### Example 3: Obtaining First and Last Days of the Previous Month

In a similar fashion, we can also compute the first and last days of the previous month, so just follow along.

📌 ** Steps**:

- In the first place, get
**today’s date as shown previously**>> enter the equation in the**C6**cell.

`=EOMONTH(C4,-2)+1`

For instance, the function returns the last day **2** months prior, then adds **1** to calculate the first day of the previous month.

- In turn, enter the
**C7**cell >> use the formula below to procure the last of the previous month.

`=EOMONTH(C4,-1)`

In this case, the **C4** cell represents *“ Today’s Date”*.

### Example 4: Calculating Number of Days in a Month

By the same token, **the DAY** and **EOMONTH** functions can return the number of days in a month.

📌 ** Steps**:

- To start with, copy and paste the expression into the
**C6**cell >> change the**Number Format**to**General**.

`=DAY(EOMONTH(C4,0))`

On this occasion, the **EOMONTH** function generates the last of the month and the **DAY **function returns the number of days in that month.

### Example 5: Returning the Working Days in a Month

Moreover, by combining the **NETWORKDAYS** and **EOMONTH** functions we can determine the total number of working days in a month.

📌 ** Steps**:

- At the very beginning, navigate to the
**D5**cell >> insert the following formula into the**Formula Bar**>> select**General**as the**Number Format**.

`=NETWORKDAYS(C5, EOMONTH(C5,0),$B$14:$B$20)`

**Formula Breakdown**

**NETWORKDAYS(C5, EOMONTH(C5,0),$B$14:$B$20)**→ returns the number of whole working days between two dates. Here, the**C5**is theargument, next,*start_date***EOMONTH(C5,0)**is theargument, and the*end_date***$B$14:$B$20**cells represent the optionalargument that is referenced from the*holidays**“Holidays”*table.**Output**→**19**

### Example 6: Computing Monthly Average Sales

In addition to this, applying the **AVERAGEIFS** and **EOMONTH** functions can allow us to obtain an average value between two dates based on a condition.

📌 ** Steps**:

- Now, in the
**Format Cells**window, choose the**Custom**tab >> type in the code**mmmm**which points to the**Month Format**>> press**OK**.

- Finally, jump to the
**C15**cell >> use the following expression.

`=AVERAGEIFS(D5:D12,B5:B12,">="&B15,B5:B12,"<="&EOMONTH(B15,0),C5:C12,"RAM")`

**Formula Breakdown**

**AVERAGEIFS(D5:D12,B5:B12,”>=”&B15,B5:B12,”<=”&EOMONTH(B15,0),C5:C12,”RAM”) →**finds average for the cells specified by a given set of conditions or criteria. Here,**D5:D12**is theargument which is the*average_range**“Sales”*column. Next,**B5:B12**is theargument which refers to the*criteria_range1**“Date”*column and the**“>=”&B15**is theargument which is the value greater than and equal to the first day of*criteria1**“January”*. Following this,**B5:B12**is theargument which refers to the*criteria_range2**“Date”*column and the**“<=”&EOMONTH(B15,0)**is theargument which represents the values less than and equal to the last day of*criteria2**“January”*. Lastly, the**C5:C12**is theargument while the*criteria_range3***“RAM”**is theargument that indicates the*criteria3**“Sales”*of*“Ram”*.**Output → $4,214**

### Example 7: Yielding Number of Monthly Occurrences

Alternatively, we can check for the number of occurrences of a value between two dates with the help of the **COUNTIF** and **EOMONTH** functions.

📌 ** Steps**:

- First and foremost,
**enter the date and apply custom formatting**in the**B15**cell as shown before >> copy and paste the equation into the**C15**cell.

`=COUNTIF(B5:B12,">"&B15)-COUNTIF(B5:B12,">"&EOMONTH(B15,0))`

**Formula Breakdown**

**COUNTIF(B5:B12,”>”&B15) →**counts the number of cells within a range that meet the given condition. Here, the**B5:B12**cells represent theargument that refers to the*range**Date*column, while the**“>”&B15**indicate theargument that returns the count of the matched value.*criteria***Output → 6**

**COUNTIF(B5:B12,”>”&EOMONTH(B15,0)) →**here, the**B5:B12**cells represent theargument that refers to the*range**Date*column, while the**“>”&EOMONTH(B15,0)**indicate theargument that returns the count of the match.*criteria***Output → 3**

**COUNTIF(B5:B12,”>”&B15)-COUNTIF(B5:B12,”>”&EOMONTH(B15,0)) →**becomes**6 – 3****→ 3**

### Example 8: Retrieving Total Monthly Sales

Conversely, we can also determine the *“ Total Sales”* using the

**SUMIFS**and

**EOMONTH**functions

📌 ** Steps**:

- Initially, follow the prior steps to enter the
**formatted month names**in the**B15:B17**cells >> type in the formula in the**C15**cell.

`=SUMIFS($D$5:$D$12,$B$5:$B$12,">="&B15,$B$5:$B$12,"<="&EOMONTH(B15,0))`

**Formula Breakdown**

**SUMIFS($D$5:$D$12,$B$5:$B$12,”>=”&B15,$B$5:$B$12,”<=”&EOMONTH(B15,0)) →**adds the cells specified by a given set of conditions or criteria. Here,**$D$5:$D$12**is theargument that refers to the*sum_range**“Sales”*column, the**$B$5:$B$12**represents theargument that points to the*criteria_range_1**“Date”*column, and the**“>=”&B15**is theargument representing the values greater than and equal to the first day of*criteria_1**“February”*. Next, the**$B$5:$B$12**is theargument which indicates the*criteria_range_2**“Date”*column and the**“<=”&EOMONTH(B15,0)**is theargument representing the values less than and equal to the last day of*criteria_2**“February”*.**Output → $13,325**

### Example 9: Checking If Date Is Within Next n Months

For one thing, we can use the **AND** in conjunction with the **EOMONTH** function to check if a date is within the next **n** months, here we’ll set the **n** to **4** months in the future.

📌 ** Steps**:

- To begin with, apply the
**TODAY**function to return today’s date in the**C15**cell.

`=TODAY()`

- Following this, enter the expression below into the
**D5**cell.

`=AND(C5>EOMONTH(TODAY(),0),C5<=EOMONTH(TODAY(),4))`

**Formula Breakdown**

**AND(C5>EOMONTH(TODAY(),0),C5<=EOMONTH(TODAY(),4)) →**checks whether all the arguments are**TRUE,**and returns**TRUE**if all the arguments are**TRUE**. Here,**C5>EOMONTH(TODAY(),0)**is theargument that checks if the date in the*logical1***C5**cell is greater than today’s date, and**C5<=EOMONTH(TODAY(),4)**is theargument that checks whether the date in the*logical2***C5**cell is less than the last day of the month**4**months in the future. Now, since the first argument is**FALSE**and the second argument is TRUE, so the**AND function**returns the output**FALSE**.**Output → FALSE**

### Example 10: Categorizing Payments Based on Months

Furthermore, we can prioritize outstanding payments with **IF**, **DATE**, and **EOMONTH** functions by listing them into groups. This means, that we can keep track of the debts that need to be paid first, due to the approaching deadline.

📌 ** Steps**:

- At the start, type in the
**DATE**function in the**C14**cell to obtain today’s date.

`=DATE(2022,12,28)`

In this case, **2022**, **12**, and **28** point to the ** year**,

**, and**

*month***arguments respectively.**

*day**📃* *Note:**You can open the **Format Cells** dialog box by pressing CTRL + 1 and changing the cell formatting to Short Date.*

- From this point, move to the
**E6**cell >> enter the expression given below >> >> drag the**Fill Handle**tool to copy the formula to the cells below.

`=IF(B6-EOMONTH($C$14,0)<=30,D6,"")`

**Formula Breakdown**

**EOMONTH($C$14,0)**→ returns the serial number of the last day of the month before or after a specified number of months. Here, the**C14**is theargument, while*start_date***0**(**zero**) is theargument representing the current month.*months***Output**→**12/31/2022**

**IF(B6-EOMONTH($C$14,0)<=30,D6,””) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**B6-EOMONTH($C$14,0)<=30**is theargument that checks if the difference between the dates*logical_test***B6-EOMONTH($C$14,0)**is less than equal to**30**. If this difference is less than or equal to**30**then the function returns**D6**(argument) otherwise it returns*value_if_true***“”**(**Blank**) (argument).*value_if_false***Output → “”**(**Blank**)

- Not long after, navigate to the
**F6**cell >> copy and paste the formula below.

`=IF(AND(B6-EOMONTH($C$14,0)>=31,B6-EOMONTH($C$14,0)<=60),D6,"")`

**Formula Breakdown**

**AND(B6-EOMONTH($C$14,0)>=31,B6-EOMONTH($C$14,0)<=60) →**here,**B6-EOMONTH($C$14,0)>=31**is theargument that checks if the difference between the two dates is greater than*logical1***31**, and**B6-EOMONTH($C$14,0)<=60**is theargument that checks whether the difference between the two dates is less than*logical2***60**. Now, since the first argument is**TRUE**and the second argument is**FALSE**so the**AND function**returns the output**FALSE**.**Output → FALSE**

**IF(AND(B6-EOMONTH($C$14,0)>=31,B6-EOMONTH($C$14,0)<=60),D6,””) →**becomes**IF(FALSE,D6,””) →**Here, the**FALSE**value of theargument prompts the function to return*logical_test***“”**(**Blank**) (argument).*value_if_false***Output → “”**(**Blank**)

- Eventually, enter the following equation into the
**G6**cell.

`=IF(B6-EOMONTH($C$14,0)>=61,D6,"")`

## How to Determine End Date of a Future Month Using EoMonth in Excel VBA

Last but not least, we can determine the end date of a future month using the **EoMonth function** in **VBA**. On this occasion, we’ll compute the last date corresponding to **6 months** into the future.

📌 ** Steps**:

- First, navigate to the
**Developer**tab >> click the**Visual Basic**button.

Now, this opens the **Visual Basic Editor** in a new window.

- Second, go to the
**Insert**tab >> select**Module**.

For ease of reference, copy the code from here and paste it into the window as shown below.

```
Sub End_Date_of_future_month()
Dim r_range As Range
Set r_range = Selection
Dim selected_cells As Range
For Each selected_cells In r_range
selected_cells.Offset(0, 1) = Application.WorksheetFunction.EoMonth(selected_cells, 6)
Next selected_cells
End Sub
```

**Code Breakdown**

Now, in the following section, we’ll explain the **VBA** code used to determine the end date of a future month.

- In the first portion, the sub-routine is given a name, here it is
**End_Date_of_future_month()**. - Next, define the variables
**r_range**and**selected_cells**variables and assign it as**Range**. - Then, use
**For Loop**to iterate through the cells in the**r_range**variable while applying the**EoMonth**function to return the end date and the**Offset property**to paste the results into the adjacent column.

- Third, close the
**VBA**window >> select the**C5:C13**cells >> click the**Macros**button >> hit**Run**.

Eventually, the results should look like the screenshot given below.

Admittedly, we have skipped some relevant examples of using the EoMonth function in VBA, which you may explore if you wish.

## Common Errors While Using the EOMONTH Function in Excel

Furthermore, in this section, we’ll discuss the possible errors we may encounter while using the **EOMONTH** function in Excel.

Error |
Occurrence |
---|---|

#NUM! |
If the start_date argument is an invalid date. |

#VALUE! |
In case the start_date argument is text. |

- In the first place, the function may return the
**#NUM!**Error if theargument contains an invalid*start_date***Date**format, like*“*.**23-23-22**”

- Additionally, we may face the
**#VALUE!**Error in case theargument is a non-numeric value, for instance, the*start_date***C4**cell refers to the text*“*.**Delivery Date**”

## Things to Remember

Here are a few things to note when using the **EOMONTH** function in Excel.

- First, a
**positive number**in theargument returns dates in the future whereas a*months***negative number**yields dates in the past. - Second, the
**EOMONTH**function ignores any decimal values given in theand*start_date*arguments.*months* - Third, by default, the
**EOMONTH**function generates the date as a serial number that needs to be formatted as a**Date**.

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

**Download Practice Workbook**

## Conclusion

In essence, this article shows 10 effective ways to use the EOMONTH function in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.