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

jonharp165

New Member
Joined
Jun 29, 2015
Messages
9
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

jonharp165

New Member
Joined
Jun 29, 2015
Messages
9
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.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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.
 

jonharp165

New Member
Joined
Jun 29, 2015
Messages
9

ADVERTISEMENT

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.
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Just do another quick check,
Enter in a blank cell
=L7<1
then the same for E & F

It should return True
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Then there is a date in it! Type over it with just the time e.g. 17:00
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

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
Top