formula to add six months to date

[lonedove288]

I haven't used Excel in a while but I need to figure a formula to change
dates by six month increments in several different columns. I can't, for the
life of me, remember how to make the formula for that. The spreadsheet is
like follows:

Name TCM date Next TCM Assess Date Next Assess Date and so on

I need the formula to update the Next TCM, Next Assess Date and so. I know
it's probably something simple and I'm just brain dead but any help would be
great.

Thanks

 

[Pete_UK]

Assuming your TCM date is in B2, put this in C2:

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

Use a similar formula for your other dates, but just ensure that the
formula points to the appropriate cell.

Hope this helps.

Pete

 

[John C]

If you have the Analysis Toolpak add-in (check under Tools-->Add-Ins), then
there is a function called EDATE.
=EDATE(A1,6)
This function is particularly nice as it takes into account the end of month
ranges.
For example:
A1: =08/31/2007
B1: using EDATE, result is 02/29/08
C1: using other formula given, result is 03/02/08

 

[Ron Rosenfeld]

I haven't used Excel in a while but I need to figure a formula to change
dates by six month increments in several different columns. I can't, for the
life of me, remember how to make the formula for that. The spreadsheet is
like follows:

Name TCM date Next TCM Assess Date Next Assess Date and so on

I need the formula to update the Next TCM, Next Assess Date and so. I know
it's probably something simple and I'm just brain dead but any help would be
great.

Thanks

The only problem with just adding 6 months to the base number, as in:

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

is the confusion that can sometimes occur when the base month has more days in
it than the resultant month. For example, the formula above, with a base date
of 31 Aug 2008, will give a result of 3 Mar 2009.

If you want to compensate for this sort of issue, you can use the following
formula:

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

or, you can use: =EDATE(a1,6)

If you get a #NAME error when you try EDATE, look at Excel HELP for EDATE and
follow the instructions to install the Analysis ToolPak.
--ron