How to calculate the number of people working different shifts at one time

jonharp165

New Member
Joined
Jun 29, 2015
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Hi,
We are trying to set up a new shift system at work which will mean different people working different overlapping hours.

I am struggling to find a formula that works. Can you help please?

I have a range of times at 30 min intervals. I want the formula to look up if there is a shift(s) at that time and if so the number of people working. And to then add them to give a total number.

I am fiddling with:
=IF($H$8="y",IF(AND(L$7>=$E$8,L$7<$F$8),$B$8))+IF($H$9="y",IF(AND(L$7>=$E$9,L$7<$F$9),$B$9))

Where:

H determines if a shift is running weekday/Saturday/ Sunday
L determines the point in time
E is the start time of the shift(s)
F s the finish time of the shift (s)
B is the number working that shift pattern

The problem as far as I can work out is that the way excel interprets time in L, E and F are different and determines answers are true even if false.

Any thoughts welcome - Thank you in advance
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Give examples of the values in L, E & F

Hi. Thanks for the quick response.
An example would be:

L. 16:00
E. 08:30. 12:00
F. 17:00. 20:00
(F is a sum where the shift length and rest break is added to the start time)

The equation should read (to my mind), if H contains value 'y' then L should be greater than or equal to E but less than F. If both are true, the show B. Then add other shift.
 
Upvote 0
It works for me! Click on each cell in turn L, E & F and check the value that appears in the formula bar, should be 08:30:00 etc, check none show a date.
 
Upvote 0
It works for me! Click on each cell in turn L, E & F and check the value that appears in the formula bar, should be 08:30:00 etc, check none show a date.

There is no date showing. I had the cells formatted as custom hh:mm as I don't want the seconds. Should this make a difference?

Maybe I'll restart the sheet from scratch and see if it make a difference.
 
Upvote 0
Just do another quick check,
Enter in a blank cell
=L7<1
then the same for E & F

It should return True
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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