Staff checker based on time stamp

[mmopulencia]

Hello Again!

Sorry if it is a bit confusing .


Example on sheet 1 i have .
[TABLE="class: cms_table_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, i have a table on sheet 2 that looks like below but the is formulated to put a 1 if thhe time stamps hits the interval. 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_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="class: cms_table, width: 1763"]
<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

 

[istiasztalos]

Hi,

You are right, the culprit is I$2>='start', in your sample data, 12:15 is still before your start time.

You'll either need to compare to the next interval, so =IF(ISBLANK($D4),"",(IF(AND(I$2<$E4,J$2>=$D4),1,""))) or add that 15 minutes, like =IF(ISBLANK($D4),"",(IF(AND(I$2<$E4,I$2+1/24/4>=$D4),1,""))) (1/24/4 is 15 minutes in Excel)

 

[mmopulencia]

Hey Man thank you for the help , im going to try this out.