Formula to give date and time

[Angela1979]

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

 

[Bernie Deitrick]

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

 

[Harlan Grove]

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)

 

[BoniM]

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 -

 

[David McRitchie]

Question was resubmitted by poster see
Add 4 hours (only if between 9am-5pm)
http://groups.google.com/groups?threadm=1176389620.146309.30330@n59g2000hsh.googlegroups.com

 

[Harlan Grove]

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.

 

[BoniM]

Ah ha! Got it... assumption I didn't even know I’d 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.