Add a Month to Any Date in Excel With a Single Formula

The fastest way to add a month to a date

To add one month to a date in Excel, use the DATE function combined with the MONTH function. The formula is =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)), where A1 is the cell containing your original date. Type this into any empty cell and Excel will return the same day one month later.

This method works because DATE rebuilds a date from three separate parts: the year, the month, and the day. By adding 1 to the MONTH part, you shift forward by exactly one calendar month. Excel automatically handles the year rollover — if your date is December 15, the formula returns January 15 of the next year without any extra work from you.

If you need to add multiple months at once, change the +1 to any number you want. For example, =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) adds three months. This same formula works whether you are adding 1 month or 12.

Key Takeaways

The DATE and MONTH formula =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) adds exactly one month to any date in cell A1.
Excel automatically rolls the year forward if you add a month to a December date, so you do not need separate logic for year changes.
To add multiple months instead of one, replace the +1 with any number — +3 adds three months, +12 adds a year.
If your original date is the 31st of a month and the next month has fewer days, Excel returns the last day of that month instead.

Understanding what happens when months have different lengths

Excel handles the edge case of unequal month lengths automatically. If you add one month to January 31, the formula returns February 28 (or 29 in a leap year), not a non-existent February 31. This is the expected behavior in most business situations — the date shifts to the last valid day of the target month.

This matters most when you are working with end-of-month dates. If your spreadsheet tracks invoices due on the 31st of each month, adding one month to January 31 will give you February 28, which may not be what you intended. In that case, you might want to manually adjust the formula or use a different approach depending on your business rules.

Step-by-step: Adding a month to a single date

Step 1: Click the cell where you want the new date to appear. This should be an empty cell, separate from the cell containing your original date. For this example, assume your original date is in cell A1 and you want the result in cell B1.

Step 2: Type the formula. Enter =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and press Enter. Excel calculates the result immediately and displays the new date in cell B1.

Step 3: Check the result. The date in B1 should be exactly one month after the date in A1, on the same day of the month. If A1 is March 15, B1 should show April 15.

Copying the formula down a column of dates

If you have a list of dates in column A and want to add one month to each of them, you only need to enter the formula once and then copy it down. Click cell B1 (where you entered the formula), then look for the small square in the bottom-right corner of the cell — this is called the fill handle.

Double-click the fill handle and Excel automatically copies the formula down to match the length of your data in column A. Each row adjusts the cell reference automatically, so row 2 becomes =DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)), row 3 becomes =DATE(YEAR(A3),MONTH(A3)+1,DAY(A3)), and so on.

If double-clicking does not work, you can also click and drag the fill handle down as far as you need, or select the range B1:B100 and press Ctrl+D (Windows) or Command+D (Mac) to fill down.

Using the EDATE function as an alternative

Excel also has a built-in EDATE function designed specifically for adding months to dates. The syntax is simpler: =EDATE(A1,1) adds one month to the date in A1. To add three months, use =EDATE(A1,3). The second number is the number of months to add, and it can be negative to subtract months instead.

EDATE handles month-length differences the same way the DATE formula does — if you add a month to January 31, it returns February 28. Many people prefer EDATE because it is shorter and easier to read, especially when adding many months at once. Both formulas produce identical results, so choose whichever feels more natural to you.

One small difference: EDATE always returns a date value, while the DATE formula returns a number that Excel formats as a date. In practice, this makes no difference to your work, but if you are troubleshooting a formula that does not look like a date, check that the cell is formatted as a date (right-click the cell, choose Format Cells, and select Date from the Category list).

Formatting the result as a date

After you enter the formula, Excel usually recognizes the result as a date and formats it automatically. However, sometimes the result appears as a number like 45000 instead of a readable date. This happens when the cell is formatted as a number instead of a date.

To fix this, right-click the cell containing the formula result and select Format Cells. In the dialog that opens, click the Number tab, then select Date from the Category list on the left. Choose the date format you prefer (such as 3/15/2024 or March 15, 2024) and click OK. The cell now displays the date in a readable format.

Frequently Asked Questions

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

No, the formula will not work if the date is stored as text. You can tell because the date is left-aligned in the cell instead of right-aligned. Convert it to a real date first by using the DATEVALUE function: =DATE(YEAR(DATEVALUE(A1)),MONTH(DATEVALUE(A1))+1,DAY(DATEVALUE(A1))). However, this only works if Excel recognizes the text as a date format.

What if I want to subtract months instead of adding them?

Use a negative number in the formula. For example, =DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)) subtracts one month. With EDATE, use =EDATE(A1,-1). Both methods work the same way in reverse.

Does the formula work in Google Sheets?

Yes, both the DATE formula and EDATE work identically in Google Sheets. The syntax is exactly the same, and the behavior with unequal month lengths is identical. You can copy a formula from Excel to Google Sheets without any changes.

What happens if I add 13 months instead of 1?

The formula treats 13 months as moving forward one year and one month. If your date is March 15, 2024, adding 13 months returns April 15, 2025. You can add any number of months — 24, 36, 100 — and Excel calculates the correct date.

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.