Counting staff per 15 minutes interval (time not exact)

[mmopulencia]

Hello Again!

HI i seem to be stuck in a rut and cant get myself out of this problem, what im trying to do is count the heads staffed in a particular interval based on their time log.

Example on sheet 1 i have .
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]date[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]Mike [/TD]
[TD]09/19/2017[/TD]
[TD]12:16 am[/TD]
[TD]3:35 am[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]09/19/2017[/TD]
[TD]1:39 am[/TD]
[TD]2:00 am[/TD]
[/TR]
</tbody>[/TABLE]


and then on sheet 2 i have

[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]date[/TD]
[TD]12:00 am[/TD]
[TD]12:15 am[/TD]
[TD]12:30 am[/TD]
[TD]12:45 am[/TD]
[TD]1:00 am[/TD]
[TD]1:15 am[/TD]
[TD]1:30 am[/TD]
[TD]1:45 am[/TD]
[TD]2:00 am[/TD]
[TD]2:15 am[/TD]
[TD]2:30 am[/TD]
[TD]2:45 am[/TD]
[TD]3:00 am

[/TD]
[TD]3:15 am[/TD]
[TD]3:30 am[/TD]
[TD]3:45 am[/TD]
[TD]4:00 am[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]09/19/2017[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]09/19/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


and the list goes on till 11:45 pm.

Thank you for your help in advance <3

 

[palaeontology]

Let's say you're looking at the time-interval 1:45 to 2:30, Mike was there the whole time, but John was only there for the first 15 minutes of that time range.

Would you be looking for an answer of 1, or 2 in that instance ?

Kind regards,

Chris

 

[Tetra201]

Not sure what level of complexity you need, but let's start with the minimal -- let's assume that:
- both sheets have identical lists of names and dates;
- the start and end time are always within the same date.

Under these assumptions, try putting the following formula in cell C2 on Sheet2 and drag-copying it across and down as needed:

=IF((C$1+"0:15">Sheet1!$C2)*(C$1 < Sheet1!$D2),1,"")<sheet1!$d2),1,"")< html=""></sheet1!$d2),1,"")<>

 

[palaeontology]

I have a system that allows you to then choose two times to investigate, for example between 11:15am to 3:45pm

I have the heading 'Name' in A2
I have the heading 'Date' in B2
I have the times 12:00am through to 11:45pm in C2:CT2
I have the staff names (eg: Mike John Frank etc) in A3:A200 (change that last cell if you have more staff than that)
Of course, I then have dates in corresponding column C and the series of 1s (obtained using Tetra's formula) in the corresponding columns from C3:CT200

In sheet 1 F3, I have a data validation drop down list of the row of times possible ... 12:00am through to 11:45pm ... this will be the 'Start' time of the investigation
I have another of these in G3 ... this will be the 'Finish' time of the investigation

In F4 I have the following formula ... =LEFT(ADDRESS(2,MATCH(F3,A2:CT2)),FIND("$",ADDRESS(2,MATCH(F3,A2:CT2)),FIND("$",ADDRESS(2,MATCH(F3,A2:CT2)))+2))&"3"

In G4 I have the following formula ... =LEFT(ADDRESS(2,MATCH(G3,A2:CT2)),FIND("$",ADDRESS(2,MATCH(G3,A2:CT2)),FIND("$",ADDRESS(2,MATCH(G3,A2:CT2)))+2))&COUNTIF(A3:A200,"<>")+2

In F5 I have the all important answer ... it tells you how many staff members were present, at some point, in that time frame chosen in cells F3 and G3 ... =SUM(N(MMULT(N(INDIRECT(F4):INDIRECT(G4)>0),TRANSPOSE(COLUMN(INDIRECT(F4):INDIRECT(G4))^0))>0))

The F5 formula is an array formula, so you'll need to enter it using ctrl shift enter, otherwise it won't work

Let me know if you have any trouble converting my cell references to what you need your end.

Kind regards,

Chris

 

[mmopulencia]

Hello Again!

Sorry if it was a bit confusing , let me try again .


Example on sheet 1 i have .
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]date[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]09/19/2017[/TD]
[TD]12:16 am[/TD]
[TD]3:35 am[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]09/19/2017[/TD]
[TD]1:39 am[/TD]
[TD]2:00 am[/TD]
[/TR]
</tbody>[/TABLE]



what i want it to look like is the table on sheet 2 which is below, i need a formula to determine whether what time the guys were in or what intervals their time slot hit.

so for example Mike his start time was 12:16 am it will fall under 12:15 am below hence the 1 mark , meaning from 12:15 to 12:30 he was there so on till 3:35 which where you'll see 3:30 having a mark of 1 as that time log falls under that category too. in summary depending on the time slot the table below will mark it as 1 if the slots given will hit those intervals.


also a future problem im also thiking about is for the dates crossing over , im looking at index matching it with time but im still 0 on it haha.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]date[/TD]
[TD]12:00 am[/TD]
[TD]12:15 am[/TD]
[TD]12:30 am[/TD]
[TD]12:45 am[/TD]
[TD]1:00 am[/TD]
[TD]1:15 am[/TD]
[TD]1:30 am[/TD]
[TD]1:45 am[/TD]
[TD]2:00 am[/TD]
[TD]2:15 am[/TD]
[TD]2:30 am[/TD]
[TD]2:45 am[/TD]
[TD]3:00 am[/TD]
[TD]3:15 am[/TD]
[TD]3:30 am[/TD]
[TD]3:45 am[/TD]
[TD]4:00 am[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]09/19/2017[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]09/19/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


i tried a couple of formulas using a simpler table below but couldnt get it to work

if you look at the entry below , it counts alright moving forward but it doesnt count interval 12:15 and doesnt allocate a '1' even though 12:29 falls under it .

with comments
formula i used is = IF(ISBLANK($D4),"",(IF(AND(I$2(12:15 am reference)<$E4(end time reference),I$2(12:15 am reference)>=$D4(Start time reference)),1,"")))

without

=IF(ISBLANK($D4),"",(IF(AND(I$2<$E4,I$2>=$D4),1,"")))


im thinking its because of this.

I$2>=$D4<--


[TABLE="width: 1763"]
<colgroup><col><col><col><col span="2"><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]HOOP[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:00 AM[/TD]
[TD]12:15 AM[/TD]
[TD]12:30 AM[/TD]
[TD]12:45 AM[/TD]
[TD]1:00 AM[/TD]
[TD]1:15 AM[/TD]
[TD]1:30 AM[/TD]
[TD]1:45 AM[/TD]
[TD]2:00 AM[/TD]
[TD]2:15 AM[/TD]
[TD]2:30 AM[/TD]
[TD]2:45 AM[/TD]
[TD]3:00 AM[/TD]
[TD]3:15 AM[/TD]
[/TR]
[TR]
[TD]WINID[/TD]
[TD]ADVISOR NAME[/TD]
[TD]SUPERVISOR NAME[/TD]
[TD]START TIME[/TD]
[TD]END TIME[/TD]
[TD]DURATION[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30228009[/TD]
[TD]MAGAT, MARY JOY[/TD]
[TD]Fenerose Miras[/TD]
[TD]12:29 AM[/TD]
[TD]3:16 AM[/TD]
[TD]2.78[/TD]
[TD]8-Jan[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


and the list goes on till 11:45 pm.

Thank you for your help in advance <3