mmopulencia
New Member
- Joined
- Feb 19, 2013
- Messages
- 30
Hello Again!
Sorry if it is a bit confusing .
Example on sheet 1 i have .
<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.
<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<--
<tbody>
</tbody>
and the list goes on till 11:45 pm.
Thank you for your help in advance <3
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