I have an Excel spreadsheet with columns of data like this:
I want to apply a filter so that only data at 10:00 and 22:00 are displayed, but when I try to apply a custom filter it comes up blank. My custom filter is 'Show rows where' DateTime equal ????-??-?? 10:00 And ????-??-?? 22:00
But it comes up empty. What am I missing?
Edited the formula, I noticed my error.
You can add a helper column with the following formula.
=IF(OR((A2-TRUNC(A2))=(1/24*22),(A2-TRUNC(A2))=(1/24*10)),"Yes","No")
And fill down.
Now you have a column that you can easily filter on since it looks at the "time"- part of the datetime cell.
1/10 and 1/22 results in the fraction that is part of the datetime of your cell.
This answer assumes the datetime is in column A and that the cells is proper datetime cells and not strings.
The value in a datetime cell is a float value of say 1.5 that translates to 1900-01-02 12:00:00.
Because it's 1.5 days after 1900-01-01.
So by using the formula 1/24 you get what one hour is in float value.
1/24*22 is the float value of the time 22:00.
So if we remove the integer part of the date time and compare against the float value times then we know what cells is 10:00 or 22:00
Add a helper column for the Hours with formula:
=Hour(A2)
Then filter on that column.
You can either just select the 10 and 22 from the dropdown, or use a custom number filter with value = 10 OR value = 22
