aggregate help

Status
Not open for further replies.

dmheller

Board Regular
Joined
May 26, 2017
Messages
119
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
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​
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
Duplicate
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,113,931
Messages
5,545,089
Members
410,652
Latest member
Zot
Top