Assume a three column database – Site ID, Reason & Date and time. The data is available in range A2:C6 of sheet1 of this workbook. The objective is to view all those records where Mains failed between 23:00 and 03:00 (irrespective of the date).
Applying a Custom Filter > Between on the Data and time column (column C) with a value of 23:00 in the “Greater than or equal to” and 03:00 in the “Less than or equal to” would be incorrect because dates are also present in the same column. The other option would be to use Data > Text to Columns to separate Date and Time into two columns. If one then applies the same custom filtering criteria on the Time column, nothing would show up – this is because MS Excel will understand 23:00 as greater then 03:00. If one reverses the custom filter criteria i.e. “Greater than or equal to” as 03:00 and “Less than or equal to” 23:00, the incorrect results would show up.
The solution is to solve this via Advanced Filters. The steps for solving are mentioned in the file.