Excel Add Months to Date: How to Do It Easily

To learn Excel Add Months to Date, we will walk you through the steps to add or subtract any number of months from a date. In case you are a beginner or an experienced Excel user, you can check below content to eliminate your questions in mind regarding edate function.

Introduction to Excel Add Months to Date

Excel allows us perform complex calculations and data analysis with ease. One of the most common tasks in Excel is adding or subtracting dates. However, adding months to a date can bring wrong results if you formulate it incorrectly. Fortunately, Excel has a function that makes it easy to add months to a date.

Excel Add Months to Date: What You Need to Know

Before we directly look at  steps to add months to a date in Excel, there are a few things you should know about edate function in excel.

• Excel stores dates as serial numbers. This means January 1, 1900 acts as the base date with a serial number 1.
• Excel recognizes dates in a variety of formats including MM/DD/YYYY and DD/MM/YYYY.
• When you add or subtract months from a date, Excel adjusts the day accordingly. For example, if you add one month to January 31st, the result will be February 28th. Or February 29th if it is in a leap year.
• Excel function we will be using to add months to a date is called EDATE.

How to Add Months to a Date in Excel

Now we all know the basics, we can try adding months to a date in Excel.

Step 1: Enter the Date

The first step is to enter the date you want to add months to in a cell. For this example, we will use the date January 1, 2022 and we will enter in cell A1.

Step 2: Enter the Number of Months to Add

Next, you can enter the number of months you want to add in a different cell. For this example, we will add 3 months in cell B1.

Step 3: Use EDATE Function

Now that we have our date and the number of months we want to add. So, we can use the EDATE function to add the months.

This formula tells Excel to add the number of months in cell B1 to the date in cell A1. The result will be a new date that is 3 months after January 1, 2022.

Step 4: Format the Cell as a Date

By default, the result of the EDATE function will be a serial number to represent the new date. In case you need to show new date in a more readable format, you need to format the cell as a date.

1. You will select the cell with the EDATE formula.
2. Then, you will right-click and select Format Cells from the context menu.
3. In this Format Cells box, you should select the Date category.
4. Again now, you choose a date format you prefer.
5. Voila, you can click OK to close this box.

FAQs

Q: Can I subtract months from a date in Excel?

A: Yes, you can use it for that. You will simply enter a negative number of months in the formula. Something like this:

This formula will subtract 3 months from the date in cell A1.

Q: How to add fractional months to a date in Excel?

A: Here you can use decimal values in the EDATE function to add fractional months. For example, if you want to add 0.5 months to a date, you can use below formula.

It will add half a month to the date in cell A1.

Q: What happens if I add months to date in excel that is at the end of a month?

A: In this situation, Excel will adjust the day accordingly. So what we mean here, if you add one month to January 31st, the result will be February 28th.

Q: What about add months to a date using a formula without using the EDATE function?

A: You can use a formula to add months to a date without using the EDATE function. One way to do this is by using the DATE function.

This date formula adds the number of months in cell B1 to the month of the date in cell A. But it will be keeping the day and year the same.

Q: How to add months to date in excel using a keyboard shortcut?

A: You can use a keyboard shortcut to add months to a date like below.

1. Select the cell with the date you want to add months to.
2. Press the “=” key on your keyboard to start a formula.
3. Type “EDATE(” and select the cell with the date you want to add months to.
4. Type a comma (“,”) and select the cell with the number of months you want to add.
5. Type “)” and press Enter.

Q: Can I use the edate function excel to add years to date in excel?

A: No, the EDATE function can only add or subtract months from a date. If you want to add or subtract years from a date, you can use DATE function instead.

how to add years to a date in excel

In this case, it adds one year to the year of the date in cell A1 while keeping the month and day the same.

Conclusion: edate function in excel

Adding months to a date in Excel is a common thing for reports or salary calculations for example. But it becomes quite simpler with EDATE function. If you understood above steps and answers, you can quickly add any number of months to a date in Excel. You may be working with dates for personal or professional purposes, knowing how to add months to a date is essential for any Excel user.

So, next time you need to add or subtract months from a date in Excel, you can get back to this tutorial to walk the steps and apply them on your Excel sheet.