#1
|
|||
|
|||
filtering a list based clock time
Thank you in advance for the help:
Here is the scene, a spread sheet, contains 5 columns, many rows as follows: Col 1 - Date Col - 2- Clock time i.e....02:25:45 Col -3 - GPM Col - 4 - W/L Col - 5 Data What I need to do: I would like to be able to filter the data by the time row, where I can eliminate all rows that do not equal my criteria. I used to have a formula that was given to me years ago (which I have lost) that went something like this: select all the data >data>filter>text filter>does not equal......... this is where I lost it. I need to be able to filter the "time" column by a selected minute , say I only want to filter the data by 5 minutes, or 10 minutes. Thanks again everyone for the help |
#2
|
|||
|
|||
Why are Date and Time separate? Is the data imported from a logger or something? If you combine them, you should be able to filter right down to the date and minute with the canned filters. For whatever reason, filtering by time alone does not give this capability.
See the attached. Select the filter above Date/Time and you will see that I have filtered for Today from 1:27 pm through 1:29 pm. |
#3
|
|||
|
|||
time
Yes the time is a critical component and is used as a basis for further analysis. the data is collected from a data logger. I had a formula (that I lost hence the request) that I could select the data set then apply a filter as I recall on a helper column to filter out "not equal to" but I don't remember how to write the helper form to separate the minutes
thank you for your response |
#4
|
|||
|
|||
Quote:
Quote:
At any rate, forget about the combined Date/Time then and just apply a filter and use the Between filter under Number Filters. If you just want from 1:27 to 1:29, enter those numbers and you're good to go. |
#5
|
|||
|
|||
time
I get your point I don't think I did a good job of describing what I need to do
I need to be able to evaluate the data set which is recorded every minute for 72 hours. I want to see only the data that represents say every five minutes or every 15 minute etc.... the data criteria may change depending on different regulatory requirements |
#6
|
|||
|
|||
Quote:
The former probably requires a helper column which should be fairly easy. 15 minutes in Excel is equal to 15/(24*60). You could set up a helper to only identify those rows where the mod() of the time with that ratio is zero or something like that. For the latter, you can group and average with a pivot, but only by minutes, hours, days, etc. To do so by 5 minutes or 15 minutes would require a helper column which should be even easier. Making it robust to manage the intervals to satisfy your regulations is mere window dressing. Should be pretty easy. |
#7
|
|||
|
|||
time
Thank you for the assistance
here is what I have come up with ...what do you think 1. Copy data to a new sheet and remove unneeded columns 2. Create a helper column and insert this code in the first cell of the helper column. 3. =(Mod(Minute(cell#), xx)) where "xx" = the number of minutes to filter by eg.. 5 or 10 or …. 4. Copy the formula down to the last row 5. Select all of the data rows 6. Select filter from the data menu 7. From the drop down arrows on the helper column select number filters > does not equal 8. In the box next to the "does not equal" enter “0” and select OK 9. Right click in the middle of the data and select delete rows. 10. You will now be left with only the rows containing the minutes selected. |
#8
|
|||
|
|||
I don't know. What do you think? Does it work?
|
#9
|
|||
|
|||
works
yes it works as I remember it. I appreciate your attempts to understand my though process it helped to write it out
thanks again. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA: Replacement of words based on a list | roderh | Excel Programming | 2 | 09-06-2015 08:54 PM |
Automatically Delete Slides based on current time | Pacman52 | PowerPoint | 2 | 07-16-2015 05:30 AM |
Vba macro code for grouping the data based on 2 hours time | dharani suresh | Excel Programming | 5 | 04-29-2014 03:25 AM |
Looking for an add-in or a way to send automatic replies based off a list | brady | Outlook | 0 | 04-19-2013 01:21 PM |
field value based on time of day | akraw | Word | 3 | 03-26-2011 08:29 PM |