All,
I have 2 equations
this first
=INDEX($E$6:$E$42184, SMALL(IF(ISNUMBER(MATCH($Q$6:$Q$42184,$W$2, 0)), MATCH(ROW($Q$6:$Q$42184), ROW($Q$6:$Q$42184)), ""), ROWS($A$1:A1)))
the 2nd
=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)),"")
The idea is that E is a date and time and q is either a 1 or 0.
neither equation gives what i am looking for. the index returns every time q has a 1 and i want to find the first 1 then skip 4 hours and find the next. The 2nd equation misses some as you can see below 9/1/20 &:34 was missed. Does anyone know an equation that would fine 9/1/20 3:56am then skip all vales of 1 till 4 hours pass then record 9/1/20 1:33pm then skip 4 hours again and find the next 9/1/20 7:34 and so on.
thansk
I have 2 equations
this first
=INDEX($E$6:$E$42184, SMALL(IF(ISNUMBER(MATCH($Q$6:$Q$42184,$W$2, 0)), MATCH(ROW($Q$6:$Q$42184), ROW($Q$6:$Q$42184)), ""), ROWS($A$1:A1)))
the 2nd
=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)),"")
The idea is that E is a date and time and q is either a 1 or 0.
neither equation gives what i am looking for. the index returns every time q has a 1 and i want to find the first 1 then skip 4 hours and find the next. The 2nd equation misses some as you can see below 9/1/20 &:34 was missed. Does anyone know an equation that would fine 9/1/20 3:56am then skip all vales of 1 till 4 hours pass then record 9/1/20 1:33pm then skip 4 hours again and find the next 9/1/20 7:34 and so on.
thansk
9/1/20 3:56:00 AM | 9/1/20 3:56:00 AM |
9/1/20 4:04:00 AM | 9/1/20 1:41:00 PM |
9/1/20 4:14:00 AM | 9/2/20 2:39:00 AM |
9/1/20 1:33:00 PM | 9/2/20 4:52:00 PM |
9/1/20 1:41:00 PM | 9/3/20 7:43:00 AM |
9/1/20 7:34:00 PM | 9/4/20 1:20:00 PM |
9/1/20 7:40:00 PM | 9/5/20 11:57:00 PM |
9/2/20 2:39:00 AM | 9/7/20 5:38:00 AM |
9/2/20 2:46:00 AM | 9/8/20 1:02:00 PM |