How to Add a Month to a Date in Excel

The simplest way: use the DATE and MONTH functions together

To add one month to a date in Excel, use this formula: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)). Replace A1 with the cell holding your date. This formula pulls the year, month, and day from your original date, adds 1 to the month number, and reassembles them into a new date.

If your date is in cell A1 and shows January 15, 2024, the formula returns February 15, 2024. If the original date is December 15, 2024, it returns January 15, 2025 — the DATE function automatically rolls the year forward when the month goes past 12.

Paste the formula into any empty cell, press Enter, and Excel displays the new date. The result is a real date value, not text, so you can use it in other calculations or sort it alongside other dates without problems.

Key Takeaways

The DATE and MONTH formula =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) adds exactly one month while keeping the day of the month the same.
When adding a month pushes you past December, Excel automatically rolls the year forward, so December 15 becomes January 15 of the next year.
If your original date is the 31st of a month and the next month has fewer days, Excel moves the result to the last day of that month instead.
You can add multiple months by changing the +1 to any number — use +3 to add three months, +12 to add a year.
The EDATE function offers a shorter alternative: =EDATE(A1,1) adds one month with less typing.

What happens when the day doesn't exist in the next month

If your date is January 31 and you add one month, Excel cannot create February 31 because that day does not exist. Instead, it moves the result to February 28 (or February 29 in a leap year). The same rule applies when adding months to any date in a 31-day month that rolls into a month with fewer days.

This behavior is usually what you want — if someone is due on the 31st of each month and you add a month to January 31, getting February 28 makes sense. But if you need different behavior, you can build a more complex formula that checks the day first. For most business uses, the standard DATE formula handles this correctly without extra steps.

Using EDATE for shorter formulas

Excel also has a dedicated function called EDATE that adds months in one step: =EDATE(A1,1). The first number in parentheses is your date cell, and the second is how many months to add. This formula is shorter to type and does the same job as the DATE and MONTH combination.

EDATE handles the 31st-day problem the same way — January 31 plus one month becomes February 28. You can use negative numbers to subtract months: =EDATE(A1,-1) removes one month from your date. For adding or subtracting multiple months, EDATE is often clearer: =EDATE(A1,6) adds six months in a single readable line.

Adding multiple months at once

To add more than one month, change the number in your formula. Using DATE and MONTH, replace the +1 with any number you need: =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) adds three months. With EDATE, change the second number: =EDATE(A1,3) does the same thing.

You can also reference another cell instead of typing a number. If cell B1 holds the number 6, the formula =EDATE(A1,B1) adds however many months are in B1. This approach works well when you have a column of dates and a column of month amounts to add — copy the formula down and it adjusts automatically for each row.

Copying the formula down a column

When you have many dates in column A and want to add a month to each one, enter the formula in cell B1, then copy it down. Click B1, copy the cell (Ctrl+C on Windows, Command+C on Mac), select the range B2 through B100 (or however many rows you have), and paste (Ctrl+V or Command+V). Excel adjusts the cell reference automatically — the formula in B2 becomes =EDATE(A2,1), in B3 it becomes =EDATE(A3,1), and so on.

You can also use the fill handle: click B1, then drag the small square at the bottom-right corner of the cell down to B100. Excel copies the formula and adjusts the references as it goes. This method is faster for small lists and gives you visual feedback as you drag.

Storing the result as a value instead of a formula

When you use a formula, Excel recalculates it every time you open the file or change something in the spreadsheet. If you want to lock in the result so it never changes, convert the formula to a value. Select the cells holding your formulas, copy them, then right-click and choose Paste Special. Click the Values radio button and click OK. Excel replaces the formulas with the dates they produced.

This step is useful when you are sharing a file and do not want the dates to shift if someone opens it on a different day, or when you are archiving data and want to preserve the exact calculation you performed. After converting to values, you can delete the original date column if you no longer need it.

Frequently Asked Questions

Can I add months to a date that is stored as text instead of a date?

No — if your date is text (like "1/15/2024" stored as words rather than a date value), the formula returns an error. First, convert the text to a real date using the DATEVALUE function: =EDATE(DATEVALUE(A1),1). This tells Excel to treat the text as a date before adding the month.

What if I need to add months but keep the result on the same day of the week?

The DATE and EDATE formulas do not preserve the day of the week — they preserve the day of the month. January 15, 2024 is a Monday, but February 15, 2024 is a Thursday. If you need a formula that moves to the same weekday, you need a more complex approach that is rarely necessary for business use. The standard formulas work for almost all scheduling and important date situations.

Can I subtract months instead of adding them?

Yes — use a negative number. =EDATE(A1,-1) removes one month from your date. =DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)) subtracts three months. The same rules about 31st days explore when subtracting.

Does the formula work if the original date is in a different date format?

Yes, as long as Excel recognizes it as a date. The formula works on dates formatted as MM/DD/YYYY, DD/MM/YYYY, or any other format Excel understands. The result takes on the date format of the cell you paste it into, so if your result cell is formatted as text, change it to a date format to see the result correctly.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.