Today I will be showing how to add 6 months to a date in Excel.
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.
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 (Quick View)
Download Practice Workbook
How 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 can achieve this in two ways:
- Using EDATE Function of Excel
- Using DATE Function of Excel
1. Using EDATE Function
To add 6 months to the dates, select the first cell and enter this formula:
=EDATE(C4,6)
As you can see, we have added 6 months to the first date.
Then drag or double click on the Fill Handle to copy the formula to the rest of the cells.
As you can see, we have added 6 months to all the dates quite handsomely.
Explanation of the Formula
- 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(C4,6)
adds 6 months with the date in cell E4 (2-Jul-2021) and returns the resultant date (2-Dec-2021). - Same for the rest of the cells.
2. Using DATE Function
If you want, you can use this alternative method to add 6 months to a date.
Select the first cell and enter this formula:
=DATE(YEAR(C4),MONTH(C4)+6,DAY(C4))
Then drag the Fill Handle to copy this formula to the rest of the cells.
As you can see, we have added 6 months to all the dates.
Explanation of the Formula
YEAR(C4)
returns the year of the date in cell C4,MONTH(C4)+6
returns the month with 6 months added to the month in cell C4, andDAY(C4)
returns the day in cell C4.- Therefore,
DATE(YEAR(C4),MONTH(C4)+6,DAY(C4))
returns the date after 6 months of the date in cell C4. - Similar for the rest of 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.
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.