How to Add 6 Months to a Date in Excel (2 Easy Ways)

Suppose we have a dataset with the Names and Joining Dates of some employees of a company. Let’s apply the EDATE and DATE functions to add 6 months to the Joining Dates.

Method 1 – Using the EDATE Function to Add 6 Months to a Date in Excel

Steps:

Select cell D5 and enter the following function:

=EDATE(C5,6)

Press Enter on your keyboard.

6 months are added to the date in cell C5 (2-Jan-2021), returning the date (2-Jul-2021).

Formula Breakdown

The EDATE function takes two arguments, start_date and months.
It adds the number of months to the start_date and returns the resultant date.
Therefore, EDATE(C5,6) adds 6 months to the date in cell C5 (2-Jan-2021) and returns the resultant date (2-Jul-2021).

Use the AutoFill feature to fill the rest of the cells in column D with the function.

Note

The EDATE function returns a #VALUE! error if the start_date argument is invalid.

Method 2: Combining DATE Function with YEAR, MONTH, and DAY Functions to Add 6 Months to a Date in Excel

We can alternatively combine the DATE function with the YEAR, MONTH, and DAY functions to add 6 months to the dates.

Steps:

Enter the following formula in cell D5 and press ENTER.

=DATE(YEAR(C5),MONTH(C5)+6,DAY(C5))

6 months are added to the date in cell C5 (2-Jan-2021), returning the resultant date (2-Jul-2021).

Formula Breakdown

YEAR(C5) returns the year of the date in cell C5, MONTH(C5)+6 returns the month plus 6 months to the month in cell C5, and DAY(C5) returns the day in cell C5.
Therefore, DATE(YEAR(C5),MONTH(C5)+6,DAY(C5)) returns the date 6 months after the date in cell C5.