how to calculate how many staff are on site each hour?

Melanie Malo

Because of Covid, we need to know the max number of staff that are at the worksite each hour.

When I had a "fixed" time frame for everyone, the formula was easy and made sense. Example, when everyone had 8:00 to 10:00 shifts and 10:00 to 12:00 shifts.

But now I have random shifts and I don't know what formula I can use to get the following result (see date row). The number of staff is much longer and the shifts are scattered throughout the day.

Paul Newcome

You are going to need some helper columns that will convert the Start and End times into numerical values. Are you using 24hr times or am/pm?

Melanie Malo

I'm using 24 h times. I also don't mind writing the times with a period instead of column (8.30; 12.45).

Paul Newcome

That's ok. We can make your current setup work with just a few helper columns and two helper rows. You are going to want to make sure your counts do not flow into the two helper columns listed out next, so go ahead and create 2 new ones way off to the far right or far left of your sheet.

[Start Time Helper]:

=VALUE(LEFT(Start@row, FIND(":", Start@row) - 1)) + (VALUE(RIGHT(Start@row), 2)) / 60)

[End Time Helper]:

=VALUE(LEFT(End@row, FIND(":", End@row) - 1)) + (VALUE(RIGHT(End@row), 2)) / 60)

Then you are going to need two helper rows. I will use rows 6 & 7 based on your above screenshot, but you can use whichever two rows work for you. In the top row you are going to enter the start hour and the bottom row would be the end hour. Just the hour is all. So using your screenshot again the two helper rows would look like this:

8.....9.....10.....11

9.....10...11.....12

These would go in the columns directly above the times you have in row 8 (so starting in column B).

Then the formula for the counts would be along the lines of...

=COUNTIFS([Start Time Helper]:[Start Time Helper], @cell <= [Column B]$7, [End Time Helper]:[End Time Helper], @cell >= [Column B]$6)