jonharp165
New Member
- Joined
- Jun 29, 2015
- Messages
- 14
- Office Version
- 2016
- Platform
- 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
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