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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
I’m trying to do the same exact thing and I am running into the same problem. Did you find a solution?
 
Upvote 0
This worked for me, a gentlemen replied to my question with this:

=SUM((A2>=$B$2:$B$32)*(A2<=$C$2:$C$32+IF($C$2:$C$32<=$B$2:$B$32,1,0)))

A column is time you want
B column is clock in time
C column is clock out time
 
Upvote 0
In future, please do not post the same question multiple times. Per Forum Rules (#12).
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Can time frames/windows be used as a condition for any functions?
Replies
4
Views
96
dallas2699
D
S
  • Question Question
Work Coverage Calendar
Replies
2
Views
594
Scoti
S
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
65
cooleomter
C
M
  • Solved
sumifs using date and time values
Replies
9
Views
322
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
Total hours minus 30 minutes
Replies
1
Views
347
Kaper
Kaper

Forum statistics

Threads
1,223,879
Messages
6,175,150
Members
452,615
Latest member
bogeys2birdies

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