Staff checker based on time stamp

[mmopulencia]

Hello Again!

Sorry if it is a bit confusing .


Example on sheet 1 i have .

Name	date	Start	End
Mike	09/19/2017	12:16 am	3:35 am
John	09/19/2017	1:39 am	2:00 am

<tbody>
</tbody>

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.

name	date	12:00 am	12:15 am	12:30 am	12:45 am	1:00 am	1:15 am	1:30 am	1:45 am	2:00 am	2:15 am	2:30 am	2:45 am	3:00 am	3:15 am	3:30 am	3:45 am	4:00 am
Mike	09/19/2017		1	1	1	1	1	1	1	1	1	1	1	1	1	1
John	09/19/2017							1	1

<tbody>
</tbody>

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<--

HOOP

7:00 AM

10:00 PM

12:00 AM

12:15 AM

12:30 AM

12:45 AM

1:00 AM

1:15 AM

1:30 AM

1:45 AM

2:00 AM

2:15 AM

2:30 AM

2:45 AM

3:00 AM

3:15 AM

WINID

ADVISOR NAME

SUPERVISOR NAME

START TIME

END TIME

DURATION

Date

30228009

MAGAT, MARY JOY

Fenerose Miras

12:29 AM

3:16 AM

2.78

8-Jan

1

1

1

1

1

1

1

1

1

1

1

1

<tbody>
</tbody>

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.