DRSteele
Well-known Member
- Joined
- Mar 31, 2015
- Messages
- 2,659
- Office Version
- 365
- Platform
- Windows
Can someone please help me?
I'm trying to create formulas to calculate the total number of hours each man is on call, segregated into the number of hours on call during waking hours, during sleeping hours, during waking hours on a holiday and during sleeping hours on a holiday. A period of being on call can span several days.
Here is some sample data and an exhibit of desired output. I include a column that illuminates how I manually calculated the hours for Man 5. [I cannot use VBA for this solution, but if one is available please do post it for the benefit of those who can.]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="bgcolor: #FFF2CC"]Holidays[/TD]
[TD="align: right"]12/25/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/26/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="bgcolor: #FFF2CC"]On Call[/TD]
[TD="bgcolor: #FFF2CC"]Man 1[/TD]
[TD="bgcolor: #FFF2CC"]Man 2[/TD]
[TD="bgcolor: #FFF2CC"]Man 3[/TD]
[TD="bgcolor: #FFF2CC"]Man 4[/TD]
[TD="bgcolor: #FFF2CC"]Man 5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]12/23/2016 21:00[/TD]
[TD="align: right"]12/24/2016 09:00[/TD]
[TD="align: right"]12/24/2016 15:00[/TD]
[TD="align: right"]12/25/2016 23:00[/TD]
[TD="align: right"]12/26/2016 22:00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]12/24/2016 03:00[/TD]
[TD="align: right"]12/24/2016 17:00[/TD]
[TD="align: right"]12/25/2016 23:00[/TD]
[TD="align: right"]12/26/2016 12:00[/TD]
[TD="align: right"]01/03/2017 04:00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]11[/TD]
[TD="bgcolor: #FFE699, align: right"]6[/TD]
[TD="bgcolor: #FFE699, align: right"]8[/TD]
[TD="bgcolor: #FFE699, align: right"]32[/TD]
[TD="bgcolor: #FFE699, align: right"]13[/TD]
[TD="bgcolor: #FFE699, align: right"]174[/TD]
[TD="align: right"][/TD]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]13[/TD]
[TD="bgcolor: #FFF2CC"]Hours On Call[/TD]
[TD="bgcolor: #FFF2CC"]Man 1[/TD]
[TD="bgcolor: #FFF2CC"]Man 2[/TD]
[TD="bgcolor: #FFF2CC"]Man 3[/TD]
[TD="bgcolor: #FFF2CC"]Man 4[/TD]
[TD="bgcolor: #FFF2CC"]Man 5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]14[/TD]
[TD="bgcolor: #F8CBAD, align: right"]1[/TD]
[TD="bgcolor: #F8CBAD, align: right"]8[/TD]
[TD="bgcolor: #F8CBAD, align: right"]7[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]96[/TD]
[TD="align: center"]15[/TD]
[TD="bgcolor: #F8CBAD, align: right"]5[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]2[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]52[/TD]
[TD="align: center"]16[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]22[/TD]
[TD="bgcolor: #F8CBAD, align: right"]6[/TD]
[TD="bgcolor: #F8CBAD, align: right"]16[/TD]
[TD="align: center"]17[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]1[/TD]
[TD="bgcolor: #F8CBAD, align: right"]7[/TD]
[TD="bgcolor: #F8CBAD, align: right"]10[/TD]
[TD="align: center"]18[/TD]
[TD="bgcolor: #FFE699, align: right"]6[/TD]
[TD="bgcolor: #FFE699, align: right"]8[/TD]
[TD="bgcolor: #FFE699, align: right"]32[/TD]
[TD="bgcolor: #FFE699, align: right"]13[/TD]
[TD="bgcolor: #FFE699, align: right"]174[/TD]
[TD="align: right"][/TD]
</tbody>
I'm trying to create formulas to calculate the total number of hours each man is on call, segregated into the number of hours on call during waking hours, during sleeping hours, during waking hours on a holiday and during sleeping hours on a holiday. A period of being on call can span several days.
Here is some sample data and an exhibit of desired output. I include a column that illuminates how I manually calculated the hours for Man 5. [I cannot use VBA for this solution, but if one is available please do post it for the benefit of those who can.]
A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
Bedtime | |||||||
Awaken | |||||||
In | |||||||
Out | |||||||
total hours | |||||||
Awake | =27,28,29,30,31,2 x16 hrs | ||||||
Asleep | =27,28,29,30,31,2 x8hrs + 3x4hrs | ||||||
Holiday Awake | =1 x 16hrs | ||||||
Holiday Asleep | =26 x2hrs + 1 x8hrs | ||||||
total hours |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="bgcolor: #FFF2CC"]Holidays[/TD]
[TD="align: right"]12/25/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/26/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="bgcolor: #FFF2CC"]On Call[/TD]
[TD="bgcolor: #FFF2CC"]Man 1[/TD]
[TD="bgcolor: #FFF2CC"]Man 2[/TD]
[TD="bgcolor: #FFF2CC"]Man 3[/TD]
[TD="bgcolor: #FFF2CC"]Man 4[/TD]
[TD="bgcolor: #FFF2CC"]Man 5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]12/23/2016 21:00[/TD]
[TD="align: right"]12/24/2016 09:00[/TD]
[TD="align: right"]12/24/2016 15:00[/TD]
[TD="align: right"]12/25/2016 23:00[/TD]
[TD="align: right"]12/26/2016 22:00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]12/24/2016 03:00[/TD]
[TD="align: right"]12/24/2016 17:00[/TD]
[TD="align: right"]12/25/2016 23:00[/TD]
[TD="align: right"]12/26/2016 12:00[/TD]
[TD="align: right"]01/03/2017 04:00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]11[/TD]
[TD="bgcolor: #FFE699, align: right"]6[/TD]
[TD="bgcolor: #FFE699, align: right"]8[/TD]
[TD="bgcolor: #FFE699, align: right"]32[/TD]
[TD="bgcolor: #FFE699, align: right"]13[/TD]
[TD="bgcolor: #FFE699, align: right"]174[/TD]
[TD="align: right"][/TD]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]13[/TD]
[TD="bgcolor: #FFF2CC"]Hours On Call[/TD]
[TD="bgcolor: #FFF2CC"]Man 1[/TD]
[TD="bgcolor: #FFF2CC"]Man 2[/TD]
[TD="bgcolor: #FFF2CC"]Man 3[/TD]
[TD="bgcolor: #FFF2CC"]Man 4[/TD]
[TD="bgcolor: #FFF2CC"]Man 5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]14[/TD]
[TD="bgcolor: #F8CBAD, align: right"]1[/TD]
[TD="bgcolor: #F8CBAD, align: right"]8[/TD]
[TD="bgcolor: #F8CBAD, align: right"]7[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]96[/TD]
[TD="align: center"]15[/TD]
[TD="bgcolor: #F8CBAD, align: right"]5[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]2[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]52[/TD]
[TD="align: center"]16[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]22[/TD]
[TD="bgcolor: #F8CBAD, align: right"]6[/TD]
[TD="bgcolor: #F8CBAD, align: right"]16[/TD]
[TD="align: center"]17[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]0[/TD]
[TD="bgcolor: #F8CBAD, align: right"]1[/TD]
[TD="bgcolor: #F8CBAD, align: right"]7[/TD]
[TD="bgcolor: #F8CBAD, align: right"]10[/TD]
[TD="align: center"]18[/TD]
[TD="bgcolor: #FFE699, align: right"]6[/TD]
[TD="bgcolor: #FFE699, align: right"]8[/TD]
[TD="bgcolor: #FFE699, align: right"]32[/TD]
[TD="bgcolor: #FFE699, align: right"]13[/TD]
[TD="bgcolor: #FFE699, align: right"]174[/TD]
[TD="align: right"][/TD]
</tbody>
Sheet22