How to Add 6 Months to a Date in Excel

Last updated on November 10th, 2026 at 10:32 am

=EDATE(A2,6) adds 6 months to the date in cell A2 in Excel. EDATE moves by whole calendar months and handles short months for you, so August 31 plus 6 months gives February 28 (or 29 in a leap year), not a date in March. If the result shows as a number such as 46143, format the cell as a date. To go back 6 months, use =EDATE(A2,-6).

How to add 6 months to a date in Excel with EDATE, subtract months, get the month end with EOMONTH, and fill a series of dates
The four formulas and tools for moving a date by months, in an illustrated overview.

Add 6 months with EDATE

  1. Put your starting date in A2, for example 1/15/2026.
  2. In B2, type =EDATE(A2,6) and press Enter.
  3. If B2 shows a number, select it and choose Short Date from the number format drop-down on the Home tab.
  4. Double-click the fill handle to copy the formula down the column.
Start (A) =EDATE(A2,6)
1/15/2026 7/15/2026
3/31/2026 9/30/2026
8/31/2026 2/28/2027
8/29/2027 2/29/2028

When the target month is shorter, EDATE uses its last day. September has 30 days, so March 31 becomes September 30.

Use a different number of months

  • Subtract 6 months: =EDATE(A2,-6)
  • Months from another cell: type the number of months in C2 and use =EDATE(A2,C2)
  • From today: =EDATE(TODAY(),6). This updates every day.
  • Add years instead: =EDATE(A2,12*2) adds 2 years. More options are in adding one year to a date in Excel.

Get the end of the month 6 months later

For billing cycles or deadlines that fall on the last day of a month, use EOMONTH:

=EOMONTH(A2,6)

For 1/15/2026, this returns 7/31/2026. To get the first day of the month 6 months later, use =EOMONTH(A2,5)+1.

Why not just add 180 days?

=A2+180 adds a fixed number of days, but 6 months is anywhere from 181 to 184 days. For January 15, adding 180 gives July 14, not July 15. Use EDATE whenever “6 months” means calendar months. Use day counts only when a rule specifies days, such as “within 180 days”. Day-based formulas are shown in adding 7 days to a date in Excel.

Why not use DATE with MONTH+6?

=DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)) works for most dates, but it rolls over at month ends. August 31 becomes March 3 the next year, because February doesn’t have 31 days. EDATE avoids that problem.

Fill a schedule every 6 months

With a formula

Put the first date in A2. In A3, type =EDATE(A2,6) and copy it down. Each row is 6 months after the one above.

Without a formula

  1. Type the first date in A2 and select it plus the cells below where the dates should go.
  2. Go to Home > Fill > Series.
  3. Choose Columns, Date, Month, and set Step value to 6.
  4. Click OK.

In Microsoft 365, =EDATE(A2,SEQUENCE(10,1,0,6)) creates 10 dates, each 6 months apart, in one formula.

Landing on a business day

If the date 6 months later must be a weekday, use =WORKDAY(EDATE(A2,6)-1,1). It returns the result date if it’s a weekday, or the next Monday if it falls on a weekend. Add a range of holiday dates as a third argument to skip those too.

Highlight dates that are coming up

To flag renewals due in the next 30 days, add a conditional formatting rule with =AND(B2>=TODAY(),B2<=TODAY()+30). Setting up the rule is covered in making Excel cells change color based on date.

Check how many months are between two dates

To confirm the gap, or to work out how many months have passed, use =DATEDIF(A2,B2,"m"). It counts complete months between the start date in A2 and the later date in B2. For A2 = 1/15/2026 and B2 = 7/15/2026, it returns 6. DATEDIF doesn’t appear in Excel’s function suggestions, but it works in every version. The later date must be in the second position, or you’ll get #NUM!.

Google Sheets and Excel for the web

EDATE, EOMONTH and DATEDIF work the same way in Excel for the web, Excel for Mac and Google Sheets. In Google Sheets, results may appear as numbers until you choose Format > Number > Date.

#VALUE! or a five-digit number

#VALUE!

The start date is text, not a real date. You’ll often see it left-aligned in the cell. Convert it with =DATEVALUE(A2), or retype it in your regional date format.

The result is a five-digit number

That’s Excel’s date serial number. Format the cell as a date.

The months count is a decimal

EDATE ignores decimals, so 6.5 is treated as 6. Round the input first if needed.