Formula to give date and time
 

Hi Everyone!!

Looking for help on a formula.

I have s apreadsheet

In Column A is a date and time 12/04/07 16:00
In column B I need to have a formula to add 4 hours to column B
but..... this is the difficult part that I am not sure is
possible....... this formula needs to take in to account days and
times - 9am - 5pm Monday - Friday.

So I need 12/04/0716:00
With formula of 9am-5pm would be 13/04/07 12:00

Any help would be great
Any questions please ask.

Thanks everyone

Formula to give date and time
 

Angela,

For a date/time in cell A2, use this formula in cell B2:

=A2+IF((A2-INT(A2)+4/24)*24 17,IF(WEEKDAY(A2,2)=5,2,0)+20/24,4/24)

and copy down to match your times.

HTH,
Bernie
MS Excel MVP


"Angela1979" wrote in message
oups.com...
Hi Everyone!!

Looking for help on a formula.

I have s apreadsheet

In Column A is a date and time 12/04/07 16:00
In column B I need to have a formula to add 4 hours to column B
but..... this is the difficult part that I am not sure is
possible....... this formula needs to take in to account days and
times - 9am - 5pm Monday - Friday.

So I need 12/04/0716:00
With formula of 9am-5pm would be 13/04/07 12:00

Any help would be great
Any questions please ask.

Thanks everyone

Formula to give date and time
 

"Angela1979" wrote...
....
In Column A is a date and time 12/04/07 16:00
In column B I need to have a formula to add 4 hours to column B

Presumably you mean add 4 hours to column A.

. . . this formula needs to take in to account days and
times - 9am - 5pm Monday - Friday.

So I need 12/04/07 16:00
With formula of 9am-5pm would be 13/04/07 12:00

B2: =A2+IF(HOUR(A2)<13,1/6,IF(WEEKDAY(A2,2)=5,2,0)+5/6)

Formula to give date and time
 

Fractions are way easier (not mention slightly more accurate) than the
decimals I've somehow gotten into the habit of using over the years...
Just one teeny thing with your formula, tho... If the start date/time is 1
pm, your formula returns 9 the next morning, instead of 5 pm.

=A2+IF(AND(HOUR(A2)<=13,MINUTE(A2)=0),1/6,IF(WEEKDAY(A2,2)=5,2,0)+5/6)

fixes that and is way better than my original - :-)

"Harlan Grove" wrote:

"Angela1979" wrote...
....
In Column A is a date and time 12/04/07 16:00
In column B I need to have a formula to add 4 hours to column B

Presumably you mean add 4 hours to column A.

. . . this formula needs to take in to account days and
times - 9am - 5pm Monday - Friday.

So I need 12/04/07 16:00
With formula of 9am-5pm would be 13/04/07 12:00

B2: =A2+IF(HOUR(A2)<13,1/6,IF(WEEKDAY(A2,2)=5,2,0)+5/6)

Formula to give date and time
 

Question was resubmitted by poster see
Add 4 hours (only if between 9am-5pm)
http://groups.google.com/groups?thre...oglegroups.com

Formula to give date and time
 

BoniM wrote...
....
Just one teeny thing with your formula, tho... If the start
date/time is 1 pm, your formula returns 9 the next morning,
instead of 5 pm.
....

If the 4 hours later in B2 is the END of the period begun in A2, I'd
agree, but there's a simpler approach.

=A2+IF(MOD(A2,1)-"13:00:01"<0,1/6,IF(WEEKDAY(A2,2)=5,2,0)+5/6)

If B2 is the beginning of the next period after the period beginning
in A2, I'll stick with 13:00 - 09:00 next workday.

Formula to give date and time
 

Ah ha! Got it... assumption I didn't even know Id made - that it was
something due back in four hours, rather than something new needed to begin.
Well either way, you've got it covered beautifully.

"Harlan Grove" wrote:

BoniM wrote...
....
Just one teeny thing with your formula, tho... If the start
date/time is 1 pm, your formula returns 9 the next morning,
instead of 5 pm.
....

If the 4 hours later in B2 is the END of the period begun in A2, I'd
agree, but there's a simpler approach.

=A2+IF(MOD(A2,1)-"13:00:01"<0,1/6,IF(WEEKDAY(A2,2)=5,2,0)+5/6)

If B2 is the beginning of the next period after the period beginning
in A2, I'll stick with 13:00 - 09:00 next workday.