Rounding time of day up to fall in during work hours
 

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!

Rounding time of day up to fall in during work hours
 

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

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

Vaya con Dios,
Chuck, CABGx3



"Jon Ratzel" wrote:

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!

Rounding time of day up to fall in during work hours
 

Or to make it a little easier to understand:

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

CLR wrote:

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

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

Vaya con Dios,
Chuck, CABGx3

"Jon Ratzel" wrote:

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!



--

Dave Peterson

Rounding time of day up to fall in during work hours
 

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?
--
David Biddulph

"Jon Ratzel" wrote in message
...
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!

Rounding time of day up to fall in during work hours
 

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

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote:

Or to make it a little easier to understand:

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

CLR wrote:

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

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

Vaya con Dios,
Chuck, CABGx3

"Jon Ratzel" wrote:

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!



--

Dave Peterson

Rounding time of day up to fall in during work hours
 

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

"CLR" wrote in message
...
Maybe this will help...........

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

Vaya con Dios,
Chuck, CABGx3

"Jon Ratzel" wrote:

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!

Rounding time of day up to fall in during work hours
 

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




"CLR" wrote:

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

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

Vaya con Dios,
Chuck, CABGx3



"Jon Ratzel" wrote:

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!

Rounding time of day up to fall in during work hours
 

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

Jon




"David Biddulph" wrote:

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?
--
David Biddulph

"Jon Ratzel" wrote in message
...
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!

Rounding time of day up to fall in during work hours
 

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

Vaya con Dios,
Chuck, CABGx3



"David Biddulph" wrote:

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

"CLR" wrote in message
...
Maybe this will help...........

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

Vaya con Dios,
Chuck, CABGx3

"Jon Ratzel" wrote:

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!