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

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

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.

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.

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo