Any other formula to add 6mth to a given dates..

[StephenL]

I had a column of dates and would like to use a formula to determine th
next dates which is 6 month away. I tried using =workday(A1,C1), wher
A1 to A50 is a series of date and C1 is 180(6mths), but the result giv
me more than 6 mths. I had tried using =date(2006,4+6,15) but i had to
many dates to key that. I understand that Excel function of "workdays
and "Networkingday" refer to 5 days week. Is there any formula o
function that I can use for 6 months for a equipment that is workin
24/7 basis, Excel don't support this

 

[Peo Sjobom]

You can use the DATE function and cell references, with your dates in
A1:A50,


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

then copy down, then you don't have to hard code each date into the date
function

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

Stephen,

My guess is that you are constructing some sort of database for re-calling
equipment for service or calibration etc?

Peo's solution is probably the easiest and cleanest to use, but you need to
be aware what happens if a calculated date doesn't exist.

If you enter 30th August into A1, and calculate six months in advance then
you get 2nd of March because the 30th February doesn't exist, this may cause
you problems if your re-call relies on it being re-called in the correct six
month slot i.e. it must still be re-called in February.

You can probably get round this by us of some IF statements in your
calculations, although I haven't tried as the current solution may be
perfectly adequate for your needs.

HTH

Neil
www.nwarwick.co.uk

 

[StephenL]

Thanks Peo Sjobom and Neil,
Neil you are right abt the February month, anyway, I'll bring forward
to 1st march.
Guess that's the only solution to my problem.

 

[daddylonglegs]

If you have analysis ToolPak installed then

=EDATE(A1,6)

If A1 contains 31st August 2005 this will return 28th February 2006

Without Analysis ToolPak this will do the same

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