#### jonharp165

##### New Member

- Joined
- Jun 29, 2015

- Messages
- 9

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