Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have two columns that contain date/time pairs as MM/DD/YYYY HH:MM:SS, and I need to filter both columns so that only times between 15:59:59 and 20:00:00 are displayed, the date does not matter. I tried doing a Greater Than filter of ??/??/???? 15:59:59 with a Less Than filter of ??/??/???? 20:00:00 but that just hides all the data. What are the correct filter parameters I should be using to display only the data I need?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

While =MOD(A1, 1) will get you the time as a number between 0.000 and 0.999…, =HOUR(A1) will get you an integer between 0 and 23.  Then display 16, 17, 18, and 19.

By the way, it turns out that =HOUR(A1) works on both actual date/time values, and also strings that look like times (with an optional date).


Edit:

As suggested above, one way to solve your problem would be to establish a helper column containing =HOUR(A1), and then filter that column to display rows containing 16, 17, 18, or 19, and hide the rest.  Another would be to set up a helper column with

=IF(AND(HOUR(A1)>=16,HOUR(A1)<20), "good", "bad")

So the rows where the time is in the desired range show up as “good” and the others show up as “bad”.  Then filter to display the “good” rows and hide the “bad” rows.  (Obviously, if the users are sensitive about the choice of words, you can change them.)  An advantage to this is that it makes it straightforward to handle multiple discriminator data columns with a single helper column.  If I understand your question correctly, you have two date/time columns, and you want to see only the rows where both values are in the late afternoon or early evening.  So put this

=IF(AND(HOUR(A1)>=16,HOUR(A1)<20,HOUR(B1)>=16,HOUR(B1)<20), "good", "bad")

into your helper column.

Now take the next step:

=IF(AND(HOUR(A1)>=16,HOUR(A1)<20,,maybe other conditions),"", "bad")

Now the rows where the time is in the desired range show up as blank and the others show up as “bad”.  Then filter to display the rows with the blank in the helper column, and hide the “bad” rows.  This way, once the filter is turned on, the users won’t see that the helper column has anything in it.

And, of course, in addition to hiding the helper column (as kobaltz suggested and teylyn explained), you can do things like putting it in Column Z (off the edge of the screen), changing the font color to white, and hiding data with a Custom Format.

share|improve this answer

You could use a formula like this

=RIGHT(A2,LEN(A2)-FIND(":",A2,1)+2)

which would take

01/31/2013 3:30PM

and return

3:30PM

From there you can do your filtering and sorting.

share|improve this answer
    
I would rather not introduce more columns into the document, the users are very picky about seeing only the data sets they want to see and I don't exactly know how they are going to use this so extra column might cause problems, at the very least will cause complaints. –  Matthew Verstraete Jul 8 '13 at 20:45
    
@kobaltz, returning the time value of any give date/time can be done much simpler: =Mod(A1,1). Matthew, unless you extract the time to a helper column, you won't be able to filter on time ranges only. The date part will always be taken into consideration when filtering. –  teylyn Jul 8 '13 at 21:09
    
@teylyn, good to know. I like making things complicated. :) I just tried the Mod and it doesn't recognize the Date Time as a number. Note sure if that would work in their case. –  kobaltz Jul 8 '13 at 21:10
1  
@kobaltz, actually, your formula does not work at all on a real date/time value. It will only work on text that looks like a date. But if the data type is a number formatted as a date time, the underlying value will be evaluated by the Right() and Find() functions and they will fail. –  teylyn Jul 8 '13 at 21:14
1  
Right-click the column header and select "Hide" from the context menu. You may want to apply the filter first. –  teylyn Jul 9 '13 at 3:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.