While working in Excel, we often have to work with dates. We have to add or subtract a specific number of days, months, or years from a date for various purposes. Undoubtedly, this is an easy and time-saving task as well. Today I will be showing how you can add 6 months to a date in Excel.
How to Add 6 Months to a Date in Excel: 2 Suitable Ways
Here we’ve got a data set with the Names and Joining Dates of some employees of a company named Johnson Group. Our objective today is to add 6 months to each of the joining dates. We will apply the EDATE and DATE functions to add 6 months to a date in Excel. Here’s an overview of the dataset for today’s task.
Method 1: Insert EDATE Function to Add 6 Months to a Date in Excel
In this section, we will use the EDATE function to add 6 months to the dates in Excel. Definitely, this is an easy and time-saving task as well. Let’s follow the instructions below to learn!
Steps:
- First of all, select cell D5 and write down the below EDATE function in that cell to add 6 months to the dates. The function is,
=EDATE(C5,6)- Hence, simply press Enter on your keyboard. Therefore, you will add 6 months with the date in cell C5 (2-Jan-2021) and returns the resultant date (2-Jul-2021) which is the return of the EDATE function.
- The EDATE function takes two arguments, called start_date and months.
- It adds the number of months with the start_date and returns the resultant date.
- Therefore, EDATE(C5,6) adds 6 months with the date in cell C5 (2-Jan-2021) and returns the resultant date (2-Jul-2021).
- Same for the rest of the cells.
- Further, we will apply the AutoFill feature to the rest of the cells with the EDATE function in column D.
- As you can see, we have added 6 months to all the dates quite handsomely.
The EDATE function returns the #VALUE! error if the start_date argument is invalid.
Read More: How to Add 3 Months to a Date in Excel
Method 2: Add 6 Months to a Date in Excel by Combining DATE Function with YEAR, MONTH, and DAY Functions
If you want, you can use this alternative method to add 6 months to a date. We will combine the DATE function with the YEAR, MONTH, and DAY functions to add 6 months to the dates. Let’s follow the instructions below to add 6 months to the dates!
Steps:
- Type the following formula in cell D5, and hit the ENTER button.
=DATE(YEAR(C5),MONTH(C5)+6,DAY(C5))- As a result, you will be able to add 6 months with the date in cell C5 (2-Jan-2021) and returns the resultant date (2-Jul-2021) of that formula.
- YEAR(C5) returns the year of the date in cell C5, MONTH(C5)+6 returns the month with 6 months added 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 after 6 months of the date in cell C5.
- Similar for the rest of the dates.
- Then drag the AutoFill Handle to copy this formula to the rest of the cells in Column D.
- As you can see, we have added 6 months to all the dates.
Read More: How to Add Months to a Date in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Using these methods, we can add 6 months to any date in Excel. Do you have any questions? Feel free to ask us.
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 | Calculate Dates | 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.