| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you subtract time as in a timecard to calculate hours work | Excel Worksheet Functions | |||
| Subtract time between certain days/work hours? | Excel Worksheet Functions | |||
| Excel Time Sheet - Rounding To Quarter Hours | Excel Worksheet Functions | |||
| Time functions, how do I work out number of hours | Excel Worksheet Functions | |||
| Work Hours Time Question | Excel Worksheet Functions | |||
![Jon Ratzel[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
