Rounding time of day up to fall in during work hours

[Jon Ratzel]

I'm working with adding and subtracting time and need a way to round a
date/time up to the next business day which runs 8:30AM-5:00PM. So, if I have
a cell that has 1/25/08 7:36 AM (which is a Friday) I would need it to round
to 1/25/08 8:30 AM. However if the time is 1/25/08 7:36 PM I would need it to
round to 1/28/08 8:30 AM which is the start of the next business day. Can
someone help?

Thanks!

 

[CLR]

Maybe this will help...........

=MAX(A1,INT(A1)+0.3541666667)

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Or to make it a little easier to understand:

=MAX(A1,INT(A1)+TIME(8,30,0))

 

[David Biddulph]

If your working day start and finish times (8:30 AM and 5:00 PM) are in C1
and D1 respectively, then try
=IF(OR(MOD(A2,1)>C$1,WEEKDAY(A2,2)>5),WORKDAY(A2,1)+B$1,IF(MOD(A2,1)<B$1,WORKDAY(A2,0)+B$1,A2))

I assume that if your time falls within the working day it stays unaltered?

 

[CLR]

Hmmmm........I thought everyone knew that .3541666667 was Excel for
8:30am...<G>

Vaya con Dios,
Chuck, CABGx3

 

[David Biddulph]

And what result does that give for the OP's second example, Chuck?

 

[Jon Ratzel]

That seems to be rounding down or up all my times to 8:30AM of the same
business day regardless of the time of day I originally had. This part works
perfect if my original time is before the 8:30AM start of the business day,
but what if my original time is after 5:00PM?

Thanks for the help, I'm getting close!

Jon

 

[Jon Ratzel]

Yep, this seems to work perfect. Thanks! I like that I can change the
parameters of my work day now more easily too.

Jon

 

[CLR]

You're right David, I didn't read close enough.........good catch.

Vaya con Dios,
Chuck, CABGx3