Rifat Hassan
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Suitable Ways to Add 6 Months to a Date in Excel
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 our 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.
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.
Conclusion
Using these methods, we can add 6 months to any date in Excel. Do you have any questions? Feel free to ask us.
” title=”
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.