How to Add Months to Date in Excel
Mar 05, 2025
Your data management in Excel significantly depends on how well you can handle dates.
Project schedules and financial forecasts are just some tasks that will require you to manipulate dates smartly. Fortunately, Microsoft Excel offers simple ways to adjust dates.
This article will focus on how to add months to a date in Excel.
Add/Subtract Months From Date Using the EDATE Function
The EDATE function is the simplest and most reliable way to add months to a date in Excel. It automatically adjusts the day value and ensures the new date remains valid. The formula for the EDATE function in Excel is:
=EDATE(start_date, months)
Explanation:
-
start_date: The original date from which you want to add months.
-
months: The number of months to add (negative values subtract months).
Example 1:
Let's assume you have the start date of various tasks in your Excel sheet and need to calculate the due date.
To add the three months in cell B2 to the date in cell A2, use =EDATE(A2,B2) and then drag the formula down.
Example 2: Subtracting months.
To subtract the same months from the same dates, enter negative numbers in the month column by adding the minus sign and repeating the process.
Ensuring the correct date format.
Sometimes, Excel may display the result as a serial number. To fix this:
-
Select the cell containing the result.
-
Go to the Home tab → Click Number Format → Select Date.
Read more: How to Convert Number to Date.
Using the DATE Function
The DATE function provides more control when manipulating dates. It allows you to modify the year, month, and day separately. See the formula for the DATE function below:
=DATE(YEAR(start_date), MONTH(start_date) + months, DAY(start_date))
Explanation:
-
YEAR(start_date) → Extracts the year from the date.
-
MONTH(start_date) + X → Adds X months to the current month.
-
DAY(start_date) → Keeps the same day.
Example.
Let's use the DATE function to add 6 months to the dates in column A. Use this formula:
=DATE(YEAR(A1), MONTH(A1) + 6, DAY(A1))
Handling end-of-month dates.
If you try this formula with January 31, 2022, you might get an incorrect date. Instead, Excel will roll over to the next month if the target month has fewer days.
For example:
To always get the last day of the month, use Method 3 (EOMONTH).
Using the EOMONTH Function (For Month-End Dates)
If you work with financial data, you may need to calculate the last day of a future month. The EOMONTH function will come in handy in such cases. This is the formula for the EOMONTH function:
=EOMONTH(start_date, months)
Explanation:
-
start_date: The reference date.
-
months: The number of months to move forward (or backward).
Example.
Let's get the last day of the month one month from January 31, 2022.
The result is February 28, 2022.
Common Issues and Their Solutions
Some issues may arise as you perform these calculations. Dates might appear as numbers, overflow into the next month, or cause errors when dealing with large month additions. Below, we’ll cover the most common issues and how to resolve them.
-
The date appears as a serial number: Excel sometimes displays date results as numbers (e.g., 45283 instead of February 20, 2025). To fix this:
- Select the cells containing the dates (or serial numbers) you want to format.
- Right-click on the selected cells and choose Format Cells (or press
Ctrl + 1). - In the Format Cells dialog box, go to the Number tab.
- From the Category list on the left, select Date.
- In the Type section on the right, choose your preferred date format (e.g.,
14/03/2024orMarch 14, 2024). - Click OK to apply the formatting.
2. Incorrect dates due to month overflow: If you add months to a 31st-day date (e.g., January 31), Excel adjusts it to the next valid date. To get the last day of the target month, use the EOMONTH function instead of DATE.
Conclusion
Adding months to dates in Excel is easy when you use the right functions. The EDATE function is the simplest and most reliable way to add months, while the DATE function allows for more customization. If you need the last day of a future or past month, the EOMONTH function is the best choice.
For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.
Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!
FAQ
1. How do I add months to a date in Excel without changing the day?
Use the EDATE function: =EDATE(A1, X), where X is the number of months to add or subtract. This keeps the day unchanged unless the new month has fewer days.
2. Why does Excel return a serial number instead of a date?
Excel stores dates as numbers, so if a date appears as a number, you need to format it. Right-click the cell, select Format Cells, choose Date, and pick your preferred format.
3. How can I subtract dates in Excel?
Use the EOMONTH function: =EOMONTH(A1, X), where X is the number of months to move forward or backward. This ensures the result is always the last day of the target month.
Related Articles
How to Print Labels From Excel Spreadsheets
Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
