Filter all records for November or 11AM or 2017 [quick tip]

Imagine you are the first officer at ship terminal αε974F1 on remote planet Alderaan. Your job involves looking at terminal log to see anomalies in time space continuum. So one day after getting to work late, thanks to crazy traffic on the floating super way in your settlement, you are looking at latest terminal log for αε974F1 on Excel (of course Excel, what else are you going to use? Notepad?!?) and want to check all the records logged at 7 AM on any day. You don’t have all the time in universe to filter records one at a time. You don’t want to write a formula or something else as it is too early in the morning and the nearest Starbucks is 7 light years away. So what would you do?

Use filters of course.

1. Simply apply filters on your data (press CTRL+Shift+L to turn on filters)
2. Click on the timestamp / data / time header and enter the time or date you want to search for.
3. For example:
   1. Type March to see all March entries
   2. 7AM to see all entries with 7AM  as hour part
   3. 2017 to see all entries logged in year 2017
   4. Or use the arrow symbol at the end of search field to search by year / month / date etc.

Here is a quick demo of how date & time filtering works.

Wait a sec, what if I want to see all records for Monday?

Alas, as of star date 11712.04 (that is December 4th, 2017), Excel hasn’t evolved to filter by Mondays. To do this, you have to extract weekday portion to another column and filter by that. You can use either =WEEKDAY([@Timestamp]) or =TEXT([@Timestamp],”DDDD”) to extract weekday portion of the date in numbers (1 to 7) or text (Sunday to Saturday).

More filtering techniques

It looks like a slow day at terminal αε974F1. Why not catch up on some Excel awesomeness while you wait for that hot brewed coffee from nearest starbucks to be teleported. Check out:

• Filter values where Fruit=Banana OR Sales >70. In Other Words, How to use Advanced Filters?
• How to Filter Odd or Even Rows only? [Quick Tips]
• Use Filter By Selected Cell’s Value to save time [Quick Tips]
• What are Pivot Table Report Filters and How to use them?
• More quick tips

My job is here is done. Beam me up Scotty.

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.