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

[jonharp165]

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

 

[jonharp165]

See my last post.

Yes working now. Shame that the formula in F reads false. Is there another way around it as it defeats the purpose of calculating it.

 

[gaz_chops]

What is the formula in F?

 

[jonharp165]

What is the formula in F?

=E+(C+D)
C is the length of shift
D is the length of break

 

[gaz_chops]

How are C & D entered? In hours & Mins like 8:00 & 0:30?

 

[jonharp165]

How are C & D entered? In hours & Mins like 8:00 & 0:30?

Yes

 

[gaz_chops]

Think I know why! If the time in E is say 16:00 when you add 8:00 & 0:30 then it become 1day & 30mins

Try amending your formula to
=mod(E+(C+D),1)

 

[jonharp165]

Think I know why! If the time in E is say 16:00 when you add 8:00 & 0:30 then it become 1day & 30mins

Try amending your formula to
=mod(E+(C+D),1)

You will probably call me an idiot but one of the values for D had a date I it but not the rest. Having changed it it now works.

I have tried the mod and it still returns a correct answer so it must also work.

Thanks for all your help. I think I should be OK from here.
Jonathan

 

[gaz_chops]

You're welcome.