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

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

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)

Quick View of How to Add 6 Months to a Date in Excel


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.

Data Set to Add 6 Months to a Date in Excel

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)

EDATE Formula to Add 6 Months to a Date in Excel

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.

Dragging the Fill Handle in Excel.

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

Formula to Add 6 Months to a Date in Excel

 

 

Then drag the Fill Handle to copy this formula to the rest of the cells.

Dragging Fill Handle to Add 6 Months to a Date in Excel

 

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, and DAY(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.


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