Hello,
I am trying to find a couple formulas to extract the amount of hours worked on each shift. Time is entered beginning and end of a shift and may include a portion of all 3 shifts. 1st shift is 6am - 2pm, 2nd shift is 2pm - 10pm, 3rd shift is 10pm - 6am. Example if I punch in at 5:45pm and punch out at 6:30 am my time should show .5 hour on 1st, 4.25 hours on 2nd, and 8 hours on 3rd.
I was able to get 3rd shift, but 1st and second shift is killing me.
Start time (A5) / End Time (B5) / First shift hours (C5) / Second shift hours (D5) / Third shift hours (E5)
Any help for cells C5 and D5 would be greatly appreciated!
Thank you
Cal
*******************
<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>
I am trying to find a couple formulas to extract the amount of hours worked on each shift. Time is entered beginning and end of a shift and may include a portion of all 3 shifts. 1st shift is 6am - 2pm, 2nd shift is 2pm - 10pm, 3rd shift is 10pm - 6am. Example if I punch in at 5:45pm and punch out at 6:30 am my time should show .5 hour on 1st, 4.25 hours on 2nd, and 8 hours on 3rd.
I was able to get 3rd shift, but 1st and second shift is killing me.
Start time (A5) / End Time (B5) / First shift hours (C5) / Second shift hours (D5) / Third shift hours (E5)
Any help for cells C5 and D5 would be greatly appreciated!
Thank you
Cal
*******************
A | B | C | D | E | |
---|---|---|---|---|---|
1 | 1st shift | 6:00:00 AM | 2:00:00 PM | ||
2 | 2nd shift | 2:00:00 PM | 10:00:00 PM | ||
3 | 3rd shift | 10:00:00 PM | 6:00:00 AM | ||
4 | START TIME | END TIME | 1st Shift | 2nd Shift | 3rd Shift |
5 | 5:56:00 PM | 6:15:00 AM | 0.25 | 4.07 | 8.00 |
6 | 4:00:00 PM | 4:00:00 AM | 6.00 | ||
7 | 12:00:00 PM | 12:15:00 AM | 2.25 | ||
8 | 5:40:00 AM | 5:00:00 PM | 0.33 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
Worksheet Formulas
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>