All,
i have a set of dates in E and a set of 1 and 0 in Q
Each time 1 shows up, i want to capture that date and time but then i want to let 4 hours pass till the next time i capture the date and time of the next 1.
I tried this where W2=1 and Z2 is were i put the equation
=IFERROR(AGGREGATE(15,6,$E$6:$E$42184/($E$6:$E$42184>(N(Z1)+"04:00:00"))/($Q$6:$Q$42184=$W$2),ROWS(Z$2:Z2)),"")
THis worked for 1 or 2 dates and times but then it skips and doesnt capture them all.
Here is what is captured vs every time there is a 1 in column q
you can see that 9/1/20 @ 740 pm is missed and so are others. Please help. thanks
i have a set of dates in E and a set of 1 and 0 in Q
Each time 1 shows up, i want to capture that date and time but then i want to let 4 hours pass till the next time i capture the date and time of the next 1.
I tried this where W2=1 and Z2 is were i put the equation
=IFERROR(AGGREGATE(15,6,$E$6:$E$42184/($E$6:$E$42184>(N(Z1)+"04:00:00"))/($Q$6:$Q$42184=$W$2),ROWS(Z$2:Z2)),"")
THis worked for 1 or 2 dates and times but then it skips and doesnt capture them all.
Here is what is captured vs every time there is a 1 in column q
you can see that 9/1/20 @ 740 pm is missed and so are others. Please help. thanks
9/1/20 3:56:00 AM | 9/1/20 3:56:00 AM | |
9/1/20 1:41:00 PM | 9/1/20 4:04:00 AM | |
9/2/20 2:39:00 AM | 9/1/20 4:14:00 AM | |
9/2/20 4:52:00 PM | 9/1/20 1:33:00 PM | |
9/3/20 7:43:00 AM | 9/1/20 1:41:00 PM | |
9/4/20 1:20:00 PM | 9/1/20 7:34:00 PM | |
9/5/20 11:57:00 PM | 9/1/20 7:40:00 PM | |
9/7/20 5:38:00 AM | 9/2/20 2:39:00 AM | |
9/8/20 1:02:00 PM | 9/2/20 2:46:00 AM | |
9/10/20 5:43:00 AM | 9/2/20 9:56:00 AM | |
9/12/20 9:03:00 AM | 9/2/20 10:07:00 AM | |
9/13/20 6:49:00 AM | 9/2/20 4:45:00 PM | |
9/15/20 2:12:00 AM | 9/2/20 4:52:00 PM | |
9/22/20 2:20:00 AM | 9/2/20 9:19:00 PM | |
9/24/20 2:41:00 PM | 9/2/20 11:49:00 PM | |
9/27/20 6:06:00 PM | 9/2/20 11:57:00 PM | |
9/3/20 5:26:00 AM |