In this tutorial, we’ll unpack the **EOMONTH** function, how to use it, and how to combine it with other functions.

The screenshot above is an overview of what we’ll cover.

## Excel EOMONTH Function: Syntax & Arguments

**Function Objective:**

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 future of the specified month.

**Version:**

The **EOMONTH **function was introduced in **Excel 2007** and is available in all later versions.

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

Suppose we have the dataset below containing *“ Product” *and

*“*columns. Using this dataset, we’ll determine the last days of each month and combine the Excel

**Delivery Date**”**EOMONTH**function with other functions to derive insights from our dataset.

### Example 1 – Basic Usage of the EOMONTH Function

**Steps**:

- In cell
**D5**, enter the**EOMONTH**function as follows:

`=EOMONTH(C5,0)`

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

**0**(

**Zero**) indicates the present month. The last day of the month in cell

**C5**is returned.

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

Let’s use the **EOMONTH **function to find the first and last days of the current month.

**Steps**:

- In cell
**C4**, enter**the TODAY function**to return today’s date:

`=TODAY()`

- In cell
**C6**, enter the following formula:

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

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

- Enter the following formula in cell
**C7**to retrieve the last day of the current month:

`=EOMONTH(TODAY(),0)`

### Example 3 – Finding the 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.

**Steps**:

- Get
**today’s date as shown previously**. - Enter the following formula in cell
**C6**:

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

This function returns the last day of the month **2** months ago, then adds **1** to calculate the first day of the following month, which is this month’s previous month.

- In cell
**C7**cell, enter the formula below to return the last day of the previous month:

`=EOMONTH(C4,-1)`

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

### Example 4 – Calculating the Number of Days in a Month

Combining **the DAY** and **EOMONTH** functions can be used to return the number of days in a month.

**Steps**:

- Enter the following formula in cell
**C6**:

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

- Change the
**Number Format**to**General**.

The **EOMONTH** function generates the last day of the month and the **DAY **function returns the number of days in that month.

### Example 5 – Calculating the Working Days in a Month

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

**Steps**:

- In cell
**D5**, enter the following formula:

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

- Select
**General**as the**Number Format**.

**Formula Breakdown**

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

### Example 6 – Computing Monthly Average Sales

By applying the **AVERAGEIFS** and **EOMONTH** functions together, we can find an average value between two dates based on a condition.

**Steps**:

- In the
**Format Cells**window that opens, choose the**Custom**tab >> type in the code**mmmm**(a**Month Format**) >> click**OK**.

- In cell
**C15**, enter the following formula:

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

### Example 7 – Finding the Number of Monthly Occurrences

We can check for the number of occurrences of a value between two dates by means of the **COUNTIF** and **EOMONTH** functions.

**Steps**:

**Enter the date and apply custom formatting**in cell**B15**as shown before.- Copy and paste the following formula into cell
**C15**:

`=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,**B5:B12**represents theargument that refers to the*range**Date*column, while**“>”&B15**indicates theargument that returns the count of the matched value.*criteria***Output → 6**

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

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

### Example 8 – Retrieving Total Monthly Sales

We can also determine the *“ Total Sales”* using the

**SUMIFS**and

**EOMONTH**functions

**Steps**:

- Follow the steps above to enter the
**formatted month names**in cells**B15:B17**. - Enter the following formula in cell
**C15**:

`=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,**$B$5:$B$12**represents theargument that points to the*criteria_range_1**“Date”*column, and**“>=”&B15**is theargument representing the values greater than or equal to the first day of*criteria_1**“February”*. Next,**$B$5:$B$12**is theargument which indicates the*criteria_range_2**“Date”*column and**“<=”&EOMONTH(B15,0)**is theargument representing the values less than or equal to the last day of*criteria_2**“February”*.**Output → $13,325**

### Example 9 – Checking If a Date Is Within the Next n Months

We can use the **AND** function 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**:

- Enter the
**TODAY**function in cell**C15**:

`=TODAY()`

- Enter the formula below in cell
**D5**:

`=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 they are.**C5>EOMONTH(TODAY(),0)**is theargument that checks if the date in cell*logical1***C5**is greater than today’s date, and**C5<=EOMONTH(TODAY(),4)**is theargument that checks whether the date in cell*logical2***C5**is less than the last day of the month**4**months in the future. Since the first argument is**FALSE**and the second argument is**TRUE**, the**AND function**returns the output**FALSE**.**Output → FALSE**

### Example 10 – Categorizing Payments Based on Months

We can prioritize outstanding payments by grouping them with the **IF**, **DATE**, and **EOMONTH** functions.

**Steps**:

- Enter the
**DATE**function below in cell**C14**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 change the cell formatting to Short Date.*

- In cell
**E6**, enter the formula below, press**Enter**, then 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,**C14**is theargument, and*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 or equal to**30**. If true, the function returns**D6**(theargument), otherwise it returns*value_if_true***“”**(**Blank**) (theargument).*value_if_false***Output → “”**(**Blank**)

- In cell
**F6**, enter 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**. Since the first argument is**TRUE**and the second argument is**FALSE**, the**AND function**returns**FALSE**.**Output → FALSE**

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

- Enter the following formula in cell
**G6**:

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

## How to Determine the 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 of the month **6 months** into the future.

**Steps**:

- Go to the
**Developer**tab >> click the**Visual Basic**button.

The **Visual Basic Editor** opens in a new window.

- Go to the
**Insert**tab >> select**Module**.

Copy the code below and paste it into the window:

```
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**

- In the first portion, the sub-routine is given a name,
**End_Date_of_future_month()**. - Next, the variables
**r_range**and**selected_cells**are defined and assigned as type**Range**. - A
**For Loop**is used to iterate through the cells in the**r_range**while applying the**EoMonth**function to return the end date and the**Offset property**to paste the results into the adjacent column.

- Close the
**VBA**window >> select the range**C5:C13**>> click the**Macros**button >> hit**Run**.

The results should look like the screenshot below.

## Common Errors While Using the EOMONTH Function in Excel

There are several errors we may encounter while using the **EOMONTH** function.

Error |
Reason |
---|---|

#NUM! |
The start_date argument is an invalid date. |

#VALUE! |
The start_date argument is text. |

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

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

## Things to Remember

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

**Download Practice Workbook**

**<< Go Back to Excel Functions ****|**** Learn Excel**