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).
- 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.
The EDATE function returns a #VALUE! error if the start_date argument is invalid.
Read More: How to Add 3 Months to a Date in Excel
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).
- 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.
- Drag the AutoFill Handle to copy this formula to the rest of the cells in Column D.
6 months are added to all the dates.
Read More: How to Add Months to a Date in Excel
Download Practice Workbook
Further Readings
- How to Add 7 Days to a Date in Excel
- How to Add 30 Days to a Date in Excel
- How to Add Days to a Date in Excel Excluding Weekends
- How to Add Weeks to a Date in Excel
- How to Add Years to a Date in Excel
- How to Add 2 Years to a Date in Excel
- How to Add 3 Years to a Date in Excel
- How to Create a Formula in Excel to Change Date by 1 Year
<< Go Back to Adding Days to Date | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Your second formula didn’t actually create any change to the dates
Hello Sam,
Thank you Sam for your response. It’s working fine now. Thank you again.