MAX testing using dates

**JakeMann** · 03-20-2013, 06:19 AM

Hi All,

I'm not 100% sure I'm using the right formula for this.

on the attached I have a line that shows that a job is running from 20/3 to 25/03. This would mean that it is running over 2 shifts per day (Morning and Afternoon)

In the main spreadsheet I'm working on I am using a Max formula because I need to know the maximum number of staff required per shift per day. This is shown in B7:D12.

What I need it to do is show the staff number over each shift per day.

So the example attached should look like -

Max staff Morning Max staff Afternoon
20/03/2013 1 1
21/03/2013 1 1
22/03/2013 1 1

etc...

Any ideas would be appreciated.

**Richard Buttrey** · 03-20-2013, 06:42 AM

Hi,

I don't understand the rules as they apply to your process data on row 2.
The number of staff required is shown in I2 although it's not clear how this single number is used to generate a maximum number somewhere else since this implies an evaluation of several numbers.

If you can describe precisely what you want to achieve with reference to your original data, state the rules and relationships and manually add some typical results for various scenarios and examples then we may be able to help further.

**JakeMann** · 03-20-2013, 07:14 AM

ok, I've stripped the spreadsheet down into something simpler.

In a 24 hour day there are 2 shift.

There are 2 jobs running on 20/03. Job A runs over 2 shifts and needs 2 staff.
Job B also runs on 20/03 but needs 1 member of staff. So the maximum staff I need on 20th is 2.
At this point the number showing in K:L2 should be 2

But Job B doesn't finish until the 22nd so in K3:L3 I need a 1 (1 being the maximum number of staff for that job over 2 shifts)

Previously I have used the formula in column N2 and O2. But this doesn't take into account that the job runs over 2 shifts on different days.

Hope I've explained myself a little more clearer...