Group Time by Categories

[jfm]

I am trying to build a formula or function that will split
a start and stop time into time categories. For example,
the three time categories would be 12:00 midnight -
7:00am, 7:01am - 5:00pm, and 5:01pm - 11:59pm. If a
surgery started at 3:00am and ended at 9:00am, the formula
or function would return 4 hours for the category 12:00
midnight to 7:00am and 2 hour for the category 7:01am to
5:00pm. Would anyone have advice on grouping times by
category? Any help is appreciated.

 

[jeff]

Hi,

I can help if you can have 3 columns, 1 each for your
3 time-shifts: assumming start time is in A13 and
stop time is in B13 put the following in

12-7am col:
=IF(AND(A13>=0,A13<=7/24),IF(B13<=7/24,(B13-A13)*24,(7/24-
A13)*24),"")

7am-5pm col:
=IF(AND(A13>7/24,A13<=17/24),IF(B13<17/24,(B13-A13)*24,
(17/24-A13)*24),IF(B13<=7/24,"",IF(A13>7/24,"",IF(AND
(B13>7/24,B13<17/24),(B13-7/24)*24,(10)))))

and finally
5pm=midnight col:
=IF(AND(A13>17/24,A13<=1),IF(B13<1,(B13-A13)*24,(1-A13)
*24),IF((B13>=17/24),(B13-17/24)*24,""))


Each col should fill in the number of hours for its
shift.

jeff

 

[Guest]

Jeff - thanks so much for your help - works great!

-----Original Message-----
Hi,

I can help if you can have 3 columns, 1 each for your
3 time-shifts: assumming start time is in A13 and
stop time is in B13 put the following in

12-7am col:
=IF(AND(A13>=0,A13<=7/24),IF(B13<=7/24,(B13-A13)*24,(7/24-
A13)*24),"")

7am-5pm col:
=IF(AND(A13>7/24,A13<=17/24),IF(B13<17/24,(B13-A13)*24,
(17/24-A13)*24),IF(B13<=7/24,"",IF(A13>7/24,"",IF(AND
(B13>7/24,B13<17/24),(B13-7/24)*24,(10)))))

and finally
5pm=midnight col:
=IF(AND(A13>17/24,A13<=1),IF(B13<1,(B13-A13)*24,(1-A13)
*24),IF((B13>=17/24),(B13-17/24)*24,""))


Each col should fill in the number of hours for its
shift.

jeff

.