CountIf falls between times

Hi,

I'm looking for some help coming up with a formula for the attached worksheet.

In the table of cells A3:G12 there is a list of staff I have working that day and the hours that they are working, which I enter manually.

The table A14:G89 is a list of matches that my staff will be monitoring which I paste in from a downloaded list. Column G is blank when I paste is filled in by the table on the right which is what I want to achieve at then end.

Once I have entered the times that people are working that day then scroll over to the right and look to fill in the column headed 'Monitoring'.

What I want help with is the 'Schedule Table' at the top of the worksheet. What I want to do here is place a formual in these cells to display how many matches a member of staff is monitoring concurrently at any given 15 minute interval. I've filled in down to the 11.05 kick-off to show what I want it to display but obviously I want to find a formula to replace this manual intervention.

Many thanks,
Adam.

Can anyone help? I'm sure this must be doable, let me know I've not made it very clear.

Thanks.

Hi Adam, try copy this formula across L4:CE12

Formula:

Please Login or Register  to view this content.

ps: you can use conditional formula to hide those zeros if you prefer not to display them on the schedule


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

ahhh... sorry, ignore that column, I can soon fix that bit.

I'm interested at the minute in putting formulas in the table at the top of the page (L4:DC12) to count how many matches each member of staff is monitoring at a time.

Alvin-Chung's formula works great.... almost!

The problem I've noticed with it now is that if I delete some fixtures from columns A:F then I get #Value errors. I can't have this as the number of rows in columns A:F will be different on a daily basis.

Can anyone offer some advice on how to alter the formula?

Hi Adam, it's hard to imagine the error that you mentioned.
Could you upload a spreadsheet with the error so that I can have a look?

Hi Alvin,


I don't have access to the computer at the moment but if you use the original example at the beginning of the thread. Then paste your formula in to L4 (and drag down and right to fill the box) then delete some fixtures from the bottom of columns A:F, you will see what I mean.

Thanks,
Adam

Hi Adam,

That's due to invalid value in column J and K, try this modified array formula:

Please Login or Register  to view this content.

ps: use ctrl+shift+enter to produce array formula


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Alvin - You are a star! That's perfect, thanks very much!

While you're such a genius can you explain one last issue? If you look at 'Phil' on my table (L12:DC12), he is greyed out all day (as if he is off duty). This is because his finish time appears to be before his start time but this is just because he starts at 4pm and finishes at 1am the following day. In fact, even if I have him starting at 01:00 to 10:00 then only the hours from 06:00 are un-greyed. You have any idea how I can get around this?

Worked out this bit...

Originally Posted by adam2308

In fact, even if I have him starting at 01:00 to 10:00 then only the hours from 06:00 are un-greyed.

Just stuck on the part where a shift starts one day and finishes the next now.

Originally Posted by adam2308

Alvin - You are a star! That's perfect, thanks very much!

While you're such a genius can you explain one last issue? If you look at 'Phil' on my table (L12:DC12), he is greyed out all day (as if he is off duty). This is because his finish time appears to be before his start time but this is just because he starts at 4pm and finishes at 1am the following day. In fact, even if I have him starting at 01:00 to 10:00 then only the hours from 06:00 are un-greyed. You have any idea how I can get around this?

Hi Adam, you're welcome

Try replace last two of your Conditional Formatting (fill with grey) with one Conditional Formatting as follow:

Formula:

Please Login or Register  to view this content.

ps: you've to change your time format in CF1:DC1 to h:mm:ss AM/PM


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin