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
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.