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

Posted on December 4th, 2017 in Quick Tip - 2 comments

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:

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

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

2 Responses to “Filter all records for November or 11AM or 2017 [quick tip]”

  1. f(x)dx says:

    Well, indeed it is possible to filter all Mondays without additional column using conditional formatting.

    Assume dates are in column A, starting from cell A4.
    Click Conditional formatting/ New rule/ Use a formula to determine which cells to format.
    Then enter this formula =WEEKDAY(A4;2)=1 (relative reference!) and chose formating at your taste. For example yellow fill.
    Copy paste format to all cells

    Then if you want to filter Mondays only - Filter by color and chose yellow filling

Leave a Reply