Counting dates within a specific range

[damiensmith212]

Hi,

My problem's a bit more complex than the title suggests but I didn't know how else to word it.

I have a workbook containing shift planners for an engineering company and am trying to automate collection of various bits of information we have to provide in monthly reports. I've been able to do the basic stuff, like counts of a specific job type, profit per client, etc with some help from Mallycat but am having trouble with a slightly more complicated problem.

For each job we put out I'm having to redesign our planners to take into account the following:

How many shifts were offered to us.
How many of those we were able to fill.
How many of those shifts were offered to us with/without 48hrs notice of the shift commencing.
If cancelled, how many were cancelled with/without 48hrs notice of the shift commencing.
When a purchase order was sent to us to cover the manpower requested by the client and if it was received by us before/after shift commencement.

I'm confused as to how I'd be able to have this information pull through from the planner sheet, to a separate tab containing all the management information. What makes it all the more difficult is there are multiple planner sheets per tab (one tab per client, several jobs per tab) and the planners themselves may have extra rows inserted to allow for more men per job than the default 4 on the blank planners on every client tab.

I've attached a sample workbook to look at. There's a job at the top of the 'A Client' tab laid out how I think the planner will have to look to contain the required information. All the other blank planners on that tab are how they used to be, before this extra information was required.

The tab that contains all the management information is on the 'MGMT INFO' tab. Both the planner on the 'A Client' tab and the relevant section on 'MGMT INFO' tab have been shaded yellow.

If the planner sheet needs a redesign to allow it to collate the information then that's fair enough. But I'm at a loss as to where to start.