Coverage By Hour

alm395

alm395

New Member
Joined
Apr 23, 2018
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Hi!

I work in a location that is 24/7 and am working on updating our schedule. I would like to be able to add a "Coverage By Hour" table, but am having trouble finding the correct formula.

We have 5 different shifts, where 4 are day shifts starting at 05:45 and ending between 15:00 - 18:15. We also have 1 night shift (18:00 - 06:00). Each shift is color-coded and time off is in purple. I tried a couple of formulas that are similar, but am not sure how else to count the number of employees we have schedule during each hour of the day.

I am on a secure workstation and was able to download the Xl2bb file but couldn't get it to capture the range, so I have included my formulas and also have a screenshot below.

Column X: I manually counted and would like the results to match these.
Column Y: =SUM(--(TIME(5,0,0)>=$B$4:$B$33)*(TIME(5,0,0)<=$C$4:$C$33)) // I updated the times for each column (6, 7, 8, etc...).
Column Z: =SUM(--($W4>=$B$4:$B$33)*($W4<=$C$4:$C$33)) // I also copied down to match the times from column W.

Once I have the formula correct, I will do a heat map on the Coverage By Hour Table to show where we are lacking staff. PLEASE HELP!!!

1710374729593.png
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Can you post your data as a table? (Just copy and paste the range).
 
Upvote 0
awoohaw said:
Can you post your data as a table? (Just copy and paste the range).
Click to expand...
I rebooted my laptop and was able to get the addon to work!

Coverage By Hour.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Mon, Feb 05Tue, Feb 06Wed, Feb 07Thu, Feb 08Fri, Feb 09
2MONTUEWEDTHUFRI
32/5/24 START2/5/24 END2/5/24 WORK2/5/24 OFF2/6/24 START2/6/24 END2/6/24 WORK2/6/24 OFF2/7/24 START2/7/24 END2/7/24 WORK2/7/24 OFF2/8/24 START2/8/24 END2/8/24 WORK2/8/24 OFF2/9/24 START2/9/24 END2/9/24 WORK2/9/24 OFF2/5/2024 MANUAL02/05/24 F102/05/024 F202/06/2402/07/2402/08/2402/09/24
4NAME 15:4518:1512.505:4518:1512.50   5:00600
5NAME 25:4518:1512.505:4518:1512.50   6:00666
6NAME 35:4518:1512.505:4513:157.505 18:006:0012.00 7:00111111
7NAME 45:4518:1512.505:4518:1512.50   8:00111111
8NAME 55:4518:1512.505:4518:1512.50 18:006:0012.00 9:00111111
9NAME 65:4518:1512.505:4518:1512.50   10:00111111
10NAME 718:006:0012.0018:006:0012.0018:006:0012.00  11:00111111
11NAME 818:006:0012.0018:006:0012.0018:006:0012.00  12:00111111
12NAME 918:006:0012.0018:006:0012.0018:006:0012.00  13:00111111
13NAME 1018:006:0012.0018:006:0012.0018:006:0012.00  14:00111111
14NAME 1118:006:0012.0018:006:0012.0018:006:0012.00  15:00111111
15NAME 1218:006:0012.00 12 12  16:00666
16NAME 137:0015:008.007:0015:008.007:0015:008.007:0015:008.00 17:00666
17NAME 147:0015:008.007:0015:008.007:0015:008.007:0015:008.00 18:001266
18NAME 157:0015:008.007:0015:008.007:0015:008.007:0015:008.00 19:00600
19NAME 16     20:00600
20NAME 177:0015:008.007:0015:008.007:0015:008.007:0015:008.00 21:00600
21NAME 187:0015:008.007:0015:008.007:0015:008.00 8 22:00600
22NAME 19  5:4518:1512.505:4518:1512.505:4518:1512.5023:00600
23NAME 20  5:4518:1512.50 12.55:4518:1512.500:00600
24NAME 21  5:4518:1512.505:4518:1512.505:4518:1512.501:00600
25NAME 22     2:00600
26NAME 23  5:4518:1512.505:4518:1512.505:4518:1512.503:00600
27NAME 24  5:4518:1512.505:4518:1512.505:4518:1512.504:00600
28NAME 25   18:006:0012.0018:006:0012.00
29NAME 26   18:006:0012.0018:006:0012.00
30NAME 27   18:006:0012.0018:006:0012.00
31NAME 28    12 12
32NAME 29   18:006:0012.0018:006:0012.00
33NAME 30    12 12
Coverage By Hr
Cell Formulas
RangeFormula
F1,J1,N1,R1F1=B1+1
B3,R3,N3,J3,F3B3=TEXT(B1,"m/d/yy")&" "&"START"
C3,S3,O3,K3,G3C3=TEXT(B1,"m/d/yy")&" "&"END"
E3,U3,Q3,M3,I3E3=TEXT(B1,"m/d/yy")&" "&"OFF"
Y4Y4=SUM(--(TIME(5,0,0)>=$B$4:$B$33)*(TIME(5,0,0)<=$C$4:$C$33))
Z4:Z27Z4=SUM(--($W4>=$B$4:$B$33)*($W4<=$C$4:$C$33))
Y5Y5=SUM(--(TIME(6,0,0)>=$B$4:$B$33)*(TIME(6,0,0)<=$C$4:$C$33))
Y6Y6=SUM(--(TIME(7,0,0)>=$B$4:$B$33)*(TIME(7,0,0)<=$C$4:$C$33))
Y7Y7=SUM(--(TIME(8,0,0)>=$B$4:$B$33)*(TIME(8,0,0)<=$C$4:$C$33))
Y8Y8=SUM(--(TIME(9,0,0)>=$B$4:$B$33)*(TIME(9,0,0)<=$C$4:$C$33))
Y9Y9=SUM(--(TIME(10,0,0)>=$B$4:$B$33)*(TIME(10,0,0)<=$C$4:$C$33))
Y10Y10=SUM(--(TIME(11,0,0)>=$B$4:$B$33)*(TIME(11,0,0)<=$C$4:$C$33))
Y11Y11=SUM(--(TIME(12,0,0)>=$B$4:$B$33)*(TIME(12,0,0)<=$C$4:$C$33))
Y12Y12=SUM(--(TIME(13,0,0)>=$B$4:$B$33)*(TIME(13,0,0)<=$C$4:$C$33))
Y13Y13=SUM(--(TIME(14,0,0)>=$B$4:$B$33)*(TIME(14,0,0)<=$C$4:$C$33))
Y14Y14=SUM(--(TIME(15,0,0)>=$B$4:$B$33)*(TIME(15,0,0)<=$C$4:$C$33))
Y15Y15=SUM(--(TIME(16,0,0)>=$B$4:$B$33)*(TIME(16,0,0)<=$C$4:$C$33))
Y16Y16=SUM(--(TIME(17,0,0)>=$B$4:$B$33)*(TIME(17,0,0)<=$C$4:$C$33))
Y17Y17=SUM(--(TIME(18,0,0)>=$B$4:$B$33)*(TIME(18,0,0)<=$C$4:$C$33))
Y18Y18=SUM(--(TIME(19,0,0)>=$B$4:$B$33)*(TIME(19,0,0)<=$C$4:$C$33))
Y19Y19=SUM(--(TIME(20,0,0)>=$B$4:$B$33)*(TIME(20,0,0)<=$C$4:$C$33))
Y20Y20=SUM(--(TIME(21,0,0)>=$B$4:$B$33)*(TIME(21,0,0)<=$C$4:$C$33))
Y21Y21=SUM(--(TIME(22,0,0)>=$B$4:$B$33)*(TIME(22,0,0)<=$C$4:$C$33))
Y22Y22=SUM(--(TIME(23,0,0)>=$B$4:$B$33)*(TIME(23,0,0)<=$C$4:$C$33))
Y23Y23=SUM(--(TIME(0,0,0)>=$B$4:$B$33)*(TIME(0,0,0)<=$C$4:$C$33))
Y24Y24=SUM(--(TIME(1,0,0)>=$B$4:$B$33)*(TIME(1,0,0)<=$C$4:$C$33))
Y25Y25=SUM(--(TIME(2,0,0)>=$B$4:$B$33)*(TIME(2,0,0)<=$C$4:$C$33))
Y26Y26=SUM(--(TIME(3,0,0)>=$B$4:$B$33)*(TIME(3,0,0)<=$C$4:$C$33))
Y27Y27=SUM(--(TIME(4,0,0)>=$B$4:$B$33)*(TIME(4,0,0)<=$C$4:$C$33))
D3,T3,P3,L3,H3D3=TEXT(B1,"m/d/yy")&" "&"WORK"
D4:D33,T4:T33,P4:P33,L4:L33,H4:H33D4=IF(AND(B4<>"",C4<>""),24*(C4-B4), "")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U4:U33,Q4:Q33,I4:I33,M4:M33,E4:E33Celldoes not contain a blank value textNO
 
Upvote 0
alm395 said:
Hi!

I work in a location that is 24/7 and am working on updating our schedule. I would like to be able to add a "Coverage By Hour" table, but am having trouble finding the correct formula.

We have 5 different shifts, where 4 are day shifts starting at 05:45 and ending between 15:00 - 18:15. We also have 1 night shift (18:00 - 06:00). Each shift is color-coded and time off is in purple. I tried a couple of formulas that are similar, but am not sure how else to count the number of employees we have schedule during each hour of the day.

I am on a secure workstation and was able to download the Xl2bb file but couldn't get it to capture the range, so I have included my formulas and also have a screenshot below.

Column X: I manually counted and would like the results to match these.
Column Y: =SUM(--(TIME(5,0,0)>=$B$4:$B$33)*(TIME(5,0,0)<=$C$4:$C$33)) // I updated the times for each column (6, 7, 8, etc...).
Column Z: =SUM(--($W4>=$B$4:$B$33)*($W4<=$C$4:$C$33)) // I also copied down to match the times from column W.

Once I have the formula correct, I will do a heat map on the Coverage By Hour Table to show where we are lacking staff. PLEASE HELP!!!
Click to expand...
Coverage By Hour.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Mon, Feb 05Tue, Feb 06Wed, Feb 07Thu, Feb 08Fri, Feb 09
2MONTUEWEDTHUFRI
32/5/24 START2/5/24 END2/5/24 WORK2/5/24 OFF2/6/24 START2/6/24 END2/6/24 WORK2/6/24 OFF2/7/24 START2/7/24 END2/7/24 WORK2/7/24 OFF2/8/24 START2/8/24 END2/8/24 WORK2/8/24 OFF2/9/24 START2/9/24 END2/9/24 WORK2/9/24 OFF2/5/2024 MANUAL02/05/24 F102/05/024 F202/06/2402/07/2402/08/2402/09/24
4NAME 15:4518:1512.505:4518:1512.50   5:00600
5NAME 25:4518:1512.505:4518:1512.50   6:00666
6NAME 35:4518:1512.505:4513:157.505 18:006:0012.00 7:00111111
7NAME 45:4518:1512.505:4518:1512.50   8:00111111
8NAME 55:4518:1512.505:4518:1512.50 18:006:0012.00 9:00111111
9NAME 65:4518:1512.505:4518:1512.50   10:00111111
10NAME 718:006:0012.0018:006:0012.0018:006:0012.00  11:00111111
11NAME 818:006:0012.0018:006:0012.0018:006:0012.00  12:00111111
12NAME 918:006:0012.0018:006:0012.0018:006:0012.00  13:00111111
13NAME 1018:006:0012.0018:006:0012.0018:006:0012.00  14:00111111
14NAME 1118:006:0012.0018:006:0012.0018:006:0012.00  15:00111111
15NAME 1218:006:0012.00 12 12  16:00666
16NAME 137:0015:008.007:0015:008.007:0015:008.007:0015:008.00 17:00666
17NAME 147:0015:008.007:0015:008.007:0015:008.007:0015:008.00 18:001266
18NAME 157:0015:008.007:0015:008.007:0015:008.007:0015:008.00 19:00600
19NAME 16     20:00600
20NAME 177:0015:008.007:0015:008.007:0015:008.007:0015:008.00 21:00600
21NAME 187:0015:008.007:0015:008.007:0015:008.00 8 22:00600
22NAME 19  5:4518:1512.505:4518:1512.505:4518:1512.5023:00600
23NAME 20  5:4518:1512.50 12.55:4518:1512.500:00600
24NAME 21  5:4518:1512.505:4518:1512.505:4518:1512.501:00600
25NAME 22     2:00600
26NAME 23  5:4518:1512.505:4518:1512.505:4518:1512.503:00600
27NAME 24  5:4518:1512.505:4518:1512.505:4518:1512.504:00600
28NAME 25   18:006:0012.0018:006:0012.00
29NAME 26   18:006:0012.0018:006:0012.00
30NAME 27   18:006:0012.0018:006:0012.00
31NAME 28    12 12
32NAME 29   18:006:0012.0018:006:0012.00
33NAME 30    12 12
Coverage By Hr
Cell Formulas
RangeFormula
F1,J1,N1,R1F1=B1+1
B3,R3,N3,J3,F3B3=TEXT(B1,"m/d/yy")&" "&"START"
C3,S3,O3,K3,G3C3=TEXT(B1,"m/d/yy")&" "&"END"
E3,U3,Q3,M3,I3E3=TEXT(B1,"m/d/yy")&" "&"OFF"
Y4Y4=SUM(--(TIME(5,0,0)>=$B$4:$B$33)*(TIME(5,0,0)<=$C$4:$C$33))
Z4:Z27Z4=SUM(--($W4>=$B$4:$B$33)*($W4<=$C$4:$C$33))
Y5Y5=SUM(--(TIME(6,0,0)>=$B$4:$B$33)*(TIME(6,0,0)<=$C$4:$C$33))
Y6Y6=SUM(--(TIME(7,0,0)>=$B$4:$B$33)*(TIME(7,0,0)<=$C$4:$C$33))
Y7Y7=SUM(--(TIME(8,0,0)>=$B$4:$B$33)*(TIME(8,0,0)<=$C$4:$C$33))
Y8Y8=SUM(--(TIME(9,0,0)>=$B$4:$B$33)*(TIME(9,0,0)<=$C$4:$C$33))
Y9Y9=SUM(--(TIME(10,0,0)>=$B$4:$B$33)*(TIME(10,0,0)<=$C$4:$C$33))
Y10Y10=SUM(--(TIME(11,0,0)>=$B$4:$B$33)*(TIME(11,0,0)<=$C$4:$C$33))
Y11Y11=SUM(--(TIME(12,0,0)>=$B$4:$B$33)*(TIME(12,0,0)<=$C$4:$C$33))
Y12Y12=SUM(--(TIME(13,0,0)>=$B$4:$B$33)*(TIME(13,0,0)<=$C$4:$C$33))
Y13Y13=SUM(--(TIME(14,0,0)>=$B$4:$B$33)*(TIME(14,0,0)<=$C$4:$C$33))
Y14Y14=SUM(--(TIME(15,0,0)>=$B$4:$B$33)*(TIME(15,0,0)<=$C$4:$C$33))
Y15Y15=SUM(--(TIME(16,0,0)>=$B$4:$B$33)*(TIME(16,0,0)<=$C$4:$C$33))
Y16Y16=SUM(--(TIME(17,0,0)>=$B$4:$B$33)*(TIME(17,0,0)<=$C$4:$C$33))
Y17Y17=SUM(--(TIME(18,0,0)>=$B$4:$B$33)*(TIME(18,0,0)<=$C$4:$C$33))
Y18Y18=SUM(--(TIME(19,0,0)>=$B$4:$B$33)*(TIME(19,0,0)<=$C$4:$C$33))
Y19Y19=SUM(--(TIME(20,0,0)>=$B$4:$B$33)*(TIME(20,0,0)<=$C$4:$C$33))
Y20Y20=SUM(--(TIME(21,0,0)>=$B$4:$B$33)*(TIME(21,0,0)<=$C$4:$C$33))
Y21Y21=SUM(--(TIME(22,0,0)>=$B$4:$B$33)*(TIME(22,0,0)<=$C$4:$C$33))
Y22Y22=SUM(--(TIME(23,0,0)>=$B$4:$B$33)*(TIME(23,0,0)<=$C$4:$C$33))
Y23Y23=SUM(--(TIME(0,0,0)>=$B$4:$B$33)*(TIME(0,0,0)<=$C$4:$C$33))
Y24Y24=SUM(--(TIME(1,0,0)>=$B$4:$B$33)*(TIME(1,0,0)<=$C$4:$C$33))
Y25Y25=SUM(--(TIME(2,0,0)>=$B$4:$B$33)*(TIME(2,0,0)<=$C$4:$C$33))
Y26Y26=SUM(--(TIME(3,0,0)>=$B$4:$B$33)*(TIME(3,0,0)<=$C$4:$C$33))
Y27Y27=SUM(--(TIME(4,0,0)>=$B$4:$B$33)*(TIME(4,0,0)<=$C$4:$C$33))
D3,T3,P3,L3,H3D3=TEXT(B1,"m/d/yy")&" "&"WORK"
D4:D33,T4:T33,P4:P33,L4:L33,H4:H33D4=IF(AND(B4<>"",C4<>""),24*(C4-B4), "")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U4:U33,Q4:Q33,I4:I33,M4:M33,E4:E33Celldoes not contain a blank value textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Work Coverage Calendar
Replies
2
Views
295
Scoti
S
C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
77
ChetanPuri
C
Ramballah
  • Question
Need a formula that counts the shifts and gives me a total
Replies
2
Views
235
bobaol
B
D
  • Question
Nested IF statements to analyze time data
Replies
6
Views
117
RobOrBob
R
J
  • Question
Shift bands
2 3
Replies
29
Views
1K
felixstraube
F

Forum statistics

Threads
1,215,071
Messages
6,122,963
Members
449,094
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top