Adding 6 Months to a Date

E

Excel User

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

Dave Peterson

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



Excel said:
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.
Click to expand...
 
G

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

Harlan Grove

kdlilly said:
How do you add 6 months and make sure the end date falls on a work day?
Click to expand...

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

Ron Rosenfeld

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

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
 
Top