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 the next dates which is 6 month away. I tried using =workday(A1,C1), where A1 to A50 is a series of date and C1 is 180(6mths), but the result give me more than 6 mths. I had tried using =date(2006,4+6,15) but i had too many dates to key that. I understand that Excel function of "workdays" and "Networkingday" refer to 5 days week. Is there any formula or function that I can use for 6 months for a equipment that is working 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


"StephenL" <[email protected]> wrote in
message news:[email protected]...
>
> I had a column of dates and would like to use a formula to determine the
> next dates which is 6 month away. I tried using =workday(A1,C1), where
> A1 to A50 is a series of date and C1 is 180(6mths), but the result give
> me more than 6 mths. I had tried using =date(2006,4+6,15) but i had too
> many dates to key that. I understand that Excel function of "workdays"
> and "Networkingday" refer to 5 days week. Is there any formula or
> function that I can use for 6 months for a equipment that is working
> 24/7 basis, Excel don't support this?
>
>
> --
> StephenL
> ------------------------------------------------------------------------
> StephenL's Profile:
> http://www.excelforum.com/member.php...o&userid=14512
> View this thread: http://www.excelforum.com/showthread...hreadid=533100
>

[Neil]

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

"Peo Sjobom" wrote:

> 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
>
>
> "StephenL" <[email protected]> wrote in
> message news:[email protected]...
> >
> > I had a column of dates and would like to use a formula to determine the
> > next dates which is 6 month away. I tried using =workday(A1,C1), where
> > A1 to A50 is a series of date and C1 is 180(6mths), but the result give
> > me more than 6 mths. I had tried using =date(2006,4+6,15) but i had too
> > many dates to key that. I understand that Excel function of "workdays"
> > and "Networkingday" refer to 5 days week. Is there any formula or
> > function that I can use for 6 months for a equipment that is working
> > 24/7 basis, Excel don't support this?
> >
> >
> > --
> > StephenL
> > ------------------------------------------------------------------------
> > StephenL's Profile:
> > http://www.excelforum.com/member.php...o&userid=14512
> > View this thread: http://www.excelforum.com/showthread...hreadid=533100
> >
>
>
>

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