How to Add 6 Months to a Date in Excel (2 Easy Ways)

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.

add 6 months to a date in excel


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.

Insert EDATE Function to Add 6 Months to a Date in Excel

Formula Breakdown
  • 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.

Notes

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.

Add 6 Months to a Date in Excel by Combining DATE Function with YEAR, MONTH, and DAY Functions

Formula Breakdown
  • 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.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. Your second formula didn’t actually create any change to the dates

Leave a reply

ExcelDemy
Logo