Adding 6 Months to a Date

[Guest]

Greetings! I have a column with dates. I need to add 6 months to those dates
and place the new dates in a new column. How do I do that? Many thanks.

 

[Dave Peterson]

=date(year(a1),month(a1)+6,day(a1))

Greetings! I have a column with dates. I need to add 6 months to those dates
and place the new dates in a new column. How do I do that? Many thanks.

 

[Guest]

=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))

 

[Greg T]

You might compare the output from Dave's formula which really adds six
months to using the EDate() function if you have the Analysis Toolpak
installed. If you don't have the toolpak installed you can use the
following formula:

=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))

[Credit to Barry Houdini for the formula.]

Check the output for adding six months to Aug 29th to Aug 31st for
example and pick which formula yields the output you desire.

 

[Harlan Grove]

How do you add 6 months and make sure the end date falls on a work day?

With or without holidays? If the date in question were a Saturday,
presumably you'd want Friday the day before, while if it were a Sunday,
presumably you'd want Monday the day after. Without holidays, try this

=DATE(YEAR(x),MONTH(x)+6,DAY(x))
+LOOKUP(WEEKDAY(DATE(YEAR(x),MONTH(x)+6,DAY(x)),3),{0;5;6},{0;-1;1})

 

[Ron Rosenfeld]

How do you add 6 months and make sure the end date falls on a work day?

If you have installed the Analysis Tool Pack, you could use a formula:

=WORKDAY(EDATE(A1,6)-1,1)

or

=WORKDAY(EDATE(A1,6)-1,1,Holidays)

where your date is in A1; and Holidays is a named range containing a list of
Holiday dates.


--ron

 

[Ron Rosenfeld]

That worked perfect. Thank you so much.

You're welcome. Thanks for the feedback.
--ron