skipping time in equation

dmheller

Board Regular
Joined
May 26, 2017
Messages
142
Office Version
  1. 365
I have an equation
=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)))
It gives a date and time. E is a list of date and times, Q is a simple 1 or 0. very simple. W2 is 1.
I look down and it has
9/1/20 3:56:00 AM​
9/1/20 4:04:00 AM​
9/1/20 4:14:00 AM​
9/1/20 1:33:00 PM​
9/1/20 1:41:00 PM​
9/1/20 7:34:00 PM​
What i would like to do it only capture one value. So 9/1/20 3:56am then skip till 9/21/20 1:33pm then 9/1/20 7:34pm. Is there a way to modify this equation to do that.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
What is the criteria for skipping times?

From your example it looks like you want to skip times that are close together but what it the minimum difference between times before the next result should be returned?
Also, should that difference be based on the previous result of the previous skipped time?
 
Upvote 0
The criteria is it needs to be greater than 4 hours. The difference can should be based on the first value so 3:56 then skip 4 hrs and find the next 1:33 then skip 4 hrs then 7:34. 4 hours is a good skip
 
Upvote 0
As long as you're using excel 2010 (or newer) this should work (as we're looking for later times I've assumed that column E is sorted in ascending order).

Note that the reference to Z1 should refer to the cell above the first formula, the reference to z$2:Z2 should refer to the cell containing the first formula. This formula does not require array confirmation with Ctrl Shift Enter.

=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)),"")

If you have excel 2019 or office 365 then it should be possible to do something similar with the MINIFS function which would most likely be more efficient given the number of rows in the data array.
 
Upvote 0
As long as you're using excel 2010 (or newer) this should work (as we're looking for later times I've assumed that column E is sorted in ascending order).

Note that the reference to Z1 should refer to the cell above the first formula, the reference to z$2:Z2 should refer to the cell containing the first formula. This formula does not require array confirmation with Ctrl Shift Enter.

=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)),"")

If you have excel 2019 or office 365 then it should be possible to do something similar with the MINIFS function which would most likely be more efficient given the number of rows in the data array.
wow, thank you. I think this works. I will sit down and pick this apart to learn what you did. Thank you again.
 
Upvote 0
wow, thank you. I think this works. I will sit down and pick this apart to learn what you did. Thank you again.
So i used that equation and it isnt giving me the correct numbers. I have messed with it a bunch. Let me show you the index data vs what the equation gives as you can see, the equation found the first and kind of the 2nd but didnt find the 9/1/20 7:34pm. If you can help, please do. thanks
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​
 
Upvote 0
As long as you're using excel 2010 (or newer) this should work (as we're looking for later times I've assumed that column E is sorted in ascending order).

Note that the reference to Z1 should refer to the cell above the first formula, the reference to z$2:Z2 should refer to the cell containing the first formula. This formula does not require array confirmation with Ctrl Shift Enter.

=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)),"")

If you have excel 2019 or office 365 then it should be possible to do something similar with the MINIFS function which would most likely be more efficient given the number of rows in the data array.
So i used that equation and it isnt giving me the correct numbers. I have messed with it a bunch. Let me show you the index data vs what the equation gives as you can see, the equation found the first and kind of the 2nd but didnt find the 9/1/20 7:34pm. If you can help, please do. thanks
9/1/20 3:56:00 AM9/1/20 3:56:00 AM
9/1/20 4:04:00 AM9/1/20 1:41:00 PM
9/1/20 4:14:00 AM9/2/20 2:39:00 AM
9/1/20 1:33:00 PM9/2/20 4:52:00 PM
9/1/20 1:41:00 PM9/3/20 7:43:00 AM
9/1/20 7:34:00 PM9/4/20 1:20:00 PM
9/1/20 7:40:00 PM9/5/20 11:57:00 PM
9/2/20 2:39:00 AM9/7/20 5:38:00 AM
 
Upvote 0
Sorry, that was my error. I didn't notice it straight away but I had mixed up 2 different methods in the formula. This one should work better.

=IF(Z1="","",IFERROR(AGGREGATE(15,6,$E$6:$E$42184/($E$6:$E$42184>(N(Z1)+"04:00:00"))/($Q$6:$Q$42184=$W$2),1),""))
 
Upvote 0
Sorry, that was my error. I didn't notice it straight away but I had mixed up 2 different methods in the formula. This one should work better.

=IF(Z1="","",IFERROR(AGGREGATE(15,6,$E$6:$E$42184/($E$6:$E$42184>(N(Z1)+"04:00:00"))/($Q$6:$Q$42184=$W$2),1),""))
Sorry to mention but z1 will always be blank so that would return blank every time correct? Am i missing something with that?
 
Upvote 0
It should work by removing part of the formula, I had assumed a text header in Z1. It is not essential but having one does make it work more efficiently. Without it the following should work.

=IFERROR(AGGREGATE(15,6,$E$6:$E$42184/($E$6:$E$42184>(Z1+"04:00:00"))/($Q$6:$Q$42184=$W$2),1),""))
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top