Staff Rota Count Help!!

**derhandy** · 09-10-2010, 04:41 AM

Hi
Hope somebody can help
I am trying to add up the amount of staff that are in work each hour of every day.
I am using a Sumproduct formulae (despite only 1 array) as i cannot think of any other way to do this task.

The issue is, is that i get a #VALUE msg when i add text in place of a shift (want to add 'DO' for Day off, 'H' for Holiday etc

I want to add the shift for each person in just 1 cell as format 12-21 etc
(start at 12noon, finish at 21.00)
so i need to use value(left formulae to extract the start time and value(right to extract the finish time of the shift.

I have attached an example and would be REALLY grateful if anyone could help me out!

Thanks

**DonkeyOte** · 09-10-2010, 06:00 AM

It would help if you outline all "text" based entries...

If we assume you have DO, H etc... ie all equate to 0 then you might find it simpler to revert to an Array

Please Login or Register  to view this content.

You can use SUMPRODUCT if you prefer but it's no more efficient than an Array (in truth) and the Array will make life a little easier for you I suspect (syntax wise)

Of course if your shifts can cross midnight things get a whole lot more complicated.