EOMONTH Function in Excel Tutorial

Excel Tutorial

Introduction

The EOMONTH function in Excel helps you find the last date of a month based on a starting date. You can use it to calculate month-end dates, payment deadlines, subscription expiry dates, and monthly reporting periods. It can also find the last date of previous or future months.

What Is the EOMONTH Function in Excel?

The EOMONTH function returns the last date of a month after adding or subtracting a specified number of months from a starting date.

For example, suppose you have a date of 15-Jan-2026 and want to find the last date of that same month.

=EOMONTH(A2,0)

Expected Result: 31-Jan-2026

The function can also calculate the last date of previous or future months.

  • 0 returns the last date of the current month.
  • 1 returns the last date of the next month.
  • 2 returns the last date of the month after next.
  • -1 returns the last date of the previous month.
  • -2 returns the last date of two months before.

EOMONTH Function Syntax

=EOMONTH(start_date,months)

Understanding the Arguments

ArgumentMeaning
start_dateThe starting date from which Excel calculates the month-end date.
monthsThe number of months to move forward or backward.

How Does the Months Argument Work?

The months argument tells Excel how many months to move from the starting date before finding the last day of that month.

Months ValueMeaning
0Last day of the current month
1Last day of the next month
2Last day of two months ahead
3Last day of three months ahead
-1Last day of the previous month
-2Last day of two months before

Example:

Suppose cell A2 contains 15-Jan-2026.

FormulaExpected Result
=EOMONTH(A2,0)31-Jan-2026
=EOMONTH(A2,1)28-Feb-2026
=EOMONTH(A2,2)31-Mar-2026
=EOMONTH(A2,-1)31-Dec-2025
=EOMONTH(A2,-2)30-Nov-2025

Important: EOMONTH returns an actual Excel date value. If the result appears as a number, format the cell as a Date.

Practical Examples of EOMONTH Function in Excel

Question 1: Find the Last Date of the Current Month

Problem Statement:

An accounts department wants to find the last date of the month for different transaction dates.

Transaction Data:

Transaction IDTransaction Date
T10105-Jan-2026
T10212-Feb-2026
T10318-Mar-2026
T10425-Apr-2026

Excel Formula:

Enter this formula in C2:

=EOMONTH(B2,0)

Expected Result:

Transaction IDTransaction DateMonth-End Date
T10105-Jan-202631-Jan-2026
T10212-Feb-202628-Feb-2026
T10318-Mar-202631-Mar-2026
T10425-Apr-202630-Apr-2026

Explanation:

The formula uses the transaction date as the starting date. The 0 tells Excel to stay in the same month and return its final date.

For example, 12-Feb-2026 becomes 28-Feb-2026 because February has 28 days in 2026.

Concept Covered: Finding the last date of the current month.

Question 2: Find the Last Date of the Next Month

Problem Statement:

A company wants to calculate the last date of the month following each employee’s joining month.

Employee Data:

Employee NameJoining Date
Rahul15-Jan-2026
Priya10-Feb-2026
Amit20-Mar-2026
Neha05-Apr-2026

Excel Formula:

Enter this formula in C2:

=EOMONTH(B2,1)

Expected Result:

Employee NameJoining DateNext Month-End
Rahul15-Jan-202628-Feb-2026
Priya10-Feb-202631-Mar-2026
Amit20-Mar-202630-Apr-2026
Neha05-Apr-202631-May-2026

Explanation:

The 1 in the formula tells Excel to move one month forward and return the last date of that month.

For Rahul, Excel moves from January to February and returns 28-Feb-2026.

Concept Covered: Calculating the last date of the following month.

Question 3: Find the Last Date of the Previous Month

Problem Statement:

An accounts team wants to identify the closing date of the previous month for each transaction.

Transaction Data:

Transaction IDTransaction Date
T20115-Jan-2026
T20210-Feb-2026
T20320-Mar-2026
T20405-Apr-2026

Excel Formula:

Enter this formula in C2:

=EOMONTH(B2,-1)

Expected Result:

Transaction IDTransaction DatePrevious Month-End
T20115-Jan-202631-Dec-2025
T20210-Feb-202631-Jan-2026
T20320-Mar-202628-Feb-2026
T20405-Apr-202631-Mar-2026

Explanation:

The -1 tells Excel to move one month backward and return the last date of that month.

For the transaction dated 10-Feb-2026, Excel returns 31-Jan-2026.

Concept Covered: Calculating previous month-end dates.

Question 4: Calculate a Subscription Expiry Date

Problem Statement:

A software company provides monthly subscriptions. The team wants to calculate the last date of the subscription month.

Subscription Data:

Customer NameSubscription Start Date
Aman05-Jan-2026
Riya12-Feb-2026
Karan20-Mar-2026
Simran08-Apr-2026

Excel Formula:

Enter this formula in C2:

=EOMONTH(B2,0)

Expected Result:

Customer NameSubscription Start DateMonth-End Date
Aman05-Jan-202631-Jan-2026
Riya12-Feb-202628-Feb-2026
Karan20-Mar-202631-Mar-2026
Simran08-Apr-202630-Apr-2026

Explanation:

The formula returns the last date of the month in which the subscription starts.

For example, a subscription starting on 20-Mar-2026 gets a month-end date of 31-Mar-2026.

Concept Covered: Calculating the month-end date for subscription records.

Question 5: Calculate the Last Date After Three Months

Problem Statement:

A company wants to calculate the last date of the month that falls three months after each project start date.

Project Data:

Project NameStart Date
Website Development10-Jan-2026
Mobile Application15-Feb-2026
Database Upgrade20-Mar-2026
Software Testing05-Apr-2026

Excel Formula:

Enter this formula in C2:

=EOMONTH(B2,3)

Expected Result:

Project NameStart DateMonth-End After 3 Months
Website Development10-Jan-202630-Apr-2026
Mobile Application15-Feb-202631-May-2026
Database Upgrade20-Mar-202630-Jun-2026
Software Testing05-Apr-202631-Jul-2026

Explanation:

The number 3 tells Excel to move three months forward from the starting date and return the last date of the resulting month.

For Website Development, Excel moves from January to April and returns 30-Apr-2026.

Concept Covered: Finding a future month-end date using a positive month value.

Question 6: Calculate the Last Date Two Months Before

Problem Statement:

A finance department wants to find the month-end date two months before each invoice date.

Invoice Data:

Invoice NumberInvoice Date
INV10115-Jan-2026
INV10210-Feb-2026
INV10320-Mar-2026
INV10405-Apr-2026

Excel Formula:

Enter this formula in C2:

=EOMONTH(B2,-2)

Expected Result:

Invoice NumberInvoice DateMonth-End Two Months Before
INV10115-Jan-202630-Nov-2025
INV10210-Feb-202631-Dec-2025
INV10320-Mar-202631-Jan-2026
INV10405-Apr-202628-Feb-2026

Explanation:

The -2 tells Excel to move two months backward before finding the final date of that month.

For INV103, Excel moves from March to January and returns 31-Jan-2026.

Concept Covered: Calculating earlier month-end dates using negative month values.

Question 7: Calculate the Financial Month-End Date

Problem Statement:

A company wants to calculate the last date of the financial month for each sales transaction. The finance team uses a three-month reporting cycle and wants to find the last date three months after each transaction month.

Sales Data:

Sales IDTransaction Date
S10112-Jan-2026
S10218-Feb-2026
S10305-Mar-2026
S10420-Apr-2026

Excel Formula:

Enter this formula in C2:

=EOMONTH(B2,3)

Expected Result:

Sales IDTransaction DateReporting Period End
S10112-Jan-202630-Apr-2026
S10218-Feb-202631-May-2026
S10305-Mar-202630-Jun-2026
S10420-Apr-202631-Jul-2026

Explanation:

The formula moves three months forward from each transaction date and returns the last date of the resulting month.

For example, a transaction dated 12-Jan-2026 returns 30-Apr-2026.

Concept Covered: Using EOMONTH to calculate future reporting period end dates.

Student Practice Tasks

Try solving these tasks using only the EOMONTH function.

Task 1: Find the Current Month-End

EmployeeJoining Date
Mohit05-May-2026
Sneha12-Jun-2026
Varun20-Jul-2026

Find the last date of each joining month.

Task 2: Find the Next Month-End

CustomerRegistration Date
Customer A10-Jan-2026
Customer B15-Feb-2026
Customer C20-Mar-2026

Find the last date of the next month for each customer.

Task 3: Find the Previous Month-End

InvoiceInvoice Date
INV20110-Apr-2026
INV20215-May-2026
INV20320-Jun-2026

Find the last date of the previous month for each invoice.

Final Student Challenge: Build a Monthly Payment Schedule

Problem Statement:

You are working in the accounts department. Your manager wants a payment schedule showing the last date of the current month, the next month, and the month after next.

Use the following data:

Customer NameInvoice Date
Rahul15-Jan-2026
Priya20-Feb-2026
Amit10-Mar-2026
Neha05-Apr-2026
Karan18-May-2026

Your Tasks:

  1. Find the last date of the current month using 0.
  2. Find the last date of the next month using 1.
  3. Find the last date of the month after next using 2.
  4. Find the last date of the previous month using -1.

Expected Report Structure:

Customer NameInvoice DateCurrent Month-EndNext Month-EndAfter Next Month-EndPrevious Month-End
Rahul15-Jan-2026Your ResultYour ResultYour ResultYour Result
Priya20-Feb-2026Your ResultYour ResultYour ResultYour Result
Amit10-Mar-2026Your ResultYour ResultYour ResultYour Result
Neha05-Apr-2026Your ResultYour ResultYour ResultYour Result
Karan18-May-2026Your ResultYour ResultYour ResultYour Result

Complete the report by applying the correct month value in each EOMONTH formula.

Key Takeaways

  • EOMONTH returns the last date of a month.
  • Its syntax is =EOMONTH(start_date,months).
  • Use 0 to find the current month’s last date.
  • Use positive numbers to move forward.
  • Use negative numbers to move backward.
  • EOMONTH automatically handles months with 28, 29, 30, or 31 days.
  • The result is a real Excel date value.
  • Format the result cell as Date if Excel displays a serial number.

Frequently Asked Questions (FAQs)

1. What is the EOMONTH function in Excel?

EOMONTH returns the last date of a month based on a starting date and a specified number of months.

2. What is the syntax of EOMONTH?

The syntax is:

=EOMONTH(start_date,months)

3. What does 0 mean in the EOMONTH function?

Zero means Excel should return the last date of the same month as the starting date.

4. How do I find the last date of the next month?

Use 1 as the months argument.

=EOMONTH(A2,1)

5. How do I find the last date of the previous month?

Use -1 as the months argument.

=EOMONTH(A2,-1)

6. Can EOMONTH calculate future dates?

Yes. You can use positive month values such as 1, 2, 3, or 6 to calculate future month-end dates.

7. Can EOMONTH calculate past dates?

Yes. Use negative month values such as -1, -2, or -3 to calculate previous month-end dates.

8. Why does EOMONTH show a number instead of a date?

Excel stores dates as serial numbers. If the result appears as a number, change the cell format to Date.

9. Does EOMONTH automatically handle February?

Yes. It automatically returns the correct last date of February, including 28 days in a normal year and 29 days in a leap year.

10. Is EOMONTH useful for monthly reports?

Yes. It is useful for calculating month-end dates for sales reports, financial reports, invoices, subscriptions, and payment schedules.