The EOMONTH function in Excel is used to calculate the end of a month’s date, based on a given date. It’s a handy tool for various financial and date-related calculations in spreadsheets.
Syntax
EOMONTH(start_date, months)
Arguments
| start_date | The initial date for which you want to find the end of the month. |
| months | The number of months to add to the start_date. It can be positive or negative to find future or past end-of-month dates. (Optional) |
How to use
The EOMONTH function is straightforward to use. Simply follow these steps:
1. Start by selecting the cell where you want the result to appear.
2. Type the formula
|
1 |
=EOMONTH(start_date, months) |
into the selected cell.
3. Replace ‘start_date’ with the cell reference or a date you want to work with. If you want to use the current date, you can simply type it as text within quotation marks, e.g., “2023-11-02”.
4. If you want to find the end of the month for a date in the future or the past, enter the number of months you’d like to add or subtract within the ‘months’ argument. This argument is optional; if omitted, Excel assumes you want to find the end of the month for the ‘start_date’ with no additional months.
5. Press Enter, and Excel will calculate and display the end-of-month date in the selected cell.
Examples
Let’s look at some examples to see how the EOMONTH function works:
Example 1: Finding the end of the current month:
|
1 |
=EOMONTH("2023-11-02") |
This formula will return the end of November 2023, which is 30th November 2023.
Example 2: Finding the end of the next month:
|
1 |
=EOMONTH("2023-11-02", 1) |
This formula will return the end of December 2023, which is 31st December 2023, as it adds one month to the start date.
Example 3: Finding the end of the previous month:
|
1 |
=EOMONTH("2023-11-02", -1) |
This formula will return the end of October 2023, which is 31st October 2023, as it subtracts one month from the start date.
Example 4: Using cell references:
|
1 |
=EOMONTH(A2, B2) |
If cell A2 contains a date, and cell B2 contains the number of months to add or subtract, this formula will calculate the end of the month based on the values in those cells.
Additional Information
The EOMONTH function is quite versatile and can be handy in financial planning, scheduling, and various other scenarios where you need to work with date calculations. It’s worth noting that the function returns a serial number, which can be formatted as a date for better readability in your spreadsheet.
