Help required with countif with many variables on a rota

andy

This is my first post so if written wrong please don’t bite.
I need to workout how many man hours are in a shift pattern broken down by hour. But each person works on different hours of the day
There each shift is broken down in 15 mins intervals.A          B          C         
Staff
Start
end
Person1
08:00
16:30
Person2
09:30
15:15
Person3
09:00
14:00
Person4
07:00
13:00
Person5
Leave
18:00

Answer that I need is        A             B
8.   07:00          1
9.   08:00          2
10. 09:00           3.5
11. 10:00           4
this shows staff in between 8 and 9 is 2 and 9 – 10 is 3.5 and so on
=COUNTIF(b$2:b$6,"07:00")+COUNTIF(b$2:b$6,"07:15")+COUNTIF(b$2:b$6,"07:30")+COUNTIF(b$2:b$6,"07:45")                giving answer 07:00=1
=COUNTIF(b$2:b$6,"08:00")+COUNTIF(b$2:b$6,"08:15")+COUNTIF(b$2:b$6,"08:30")+COUNTIF(b$2:b$6,"08:45")+B8            giving answer 08:00=2
but using above only gives a count of 1 per person which is great but. I require
07:00 = 1  07:15 = 0.75  07:30 = 0.5  07:45 = 0.25
I also have a count for the end time which the deducts the start time
1 question is how I mix in the countif and above
to make thing even harder as you can see person5 does not have a time stamp for a start time . as showing on leave for that morning but in for the afternoon but has a end time this basically means in for half a day but this will not include in the count. Also dinners need to be taken in to consideration but only if allowed. Ie over 6 hours dinners allowed if not and if start at 07:00 go for lunch at 11 08:00 12 and so on but the 15 min interval blow this out too
please help as this is driving me Nutts

I can assist with a piece of this. To do your counts of personnel by quarter hours you can use the sumproduct function rather that the count if function.
=SUMPRODUCT(--($B$2B$5A10))
See the embedded sheet for more info. Note, this doesn't take into account all of your requirements, but I hope it points you in the right direction.
Take care,
Owen

******** ******************** src="from:>*********>sun/puremis/colo/popup.js">*********>cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Book2.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp :void(0)" target="_blank">(A)boutA10))" selected>B10A11))">B11A12))">B12A13))">B13A14))">B14A15))">B15A16))">B16A17))">B17A18))">B18A19))">B19A20))">B20A21))">B21A22))">B22=A10))" name=txbFb426622>

ABCD1StaffStartend 2Person18:0016:30 3Person29:3015:15 4Person39:0014:00 5Person47:0013:00 6Person5Leave18:00 789Personnel per Quarter Hour 107:00:void(0);" target="_blank">1 117:15:void(0);" target="_blank">1 127:30:void(0);" target="_blank">1 137:45:void(0);" target="_blank">1 148:00:void(0);" target="_blank">2 158:15:void(0);" target="_blank">2 168:30:void(0);" target="_blank">2 178:45:void(0);" target="_blank">2 189:00:void(0);" target="_blank">3 199:15:void(0);" target="_blank">3 209:30:void(0);" target="_blank">4 219:45:void(0);" target="_blank">4 2210:00:void(0);" target="_blank">4 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.