Filter on a column of Date and time values

{ 4 Comments }

By Ashish Mathur · · , · Leave a comment

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.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • I’m working on a report of orders worked from September 2008 through February 16, 2009. I have the orders sorted by date and time and I need to insert rows between each day and each shift. I wrote a macro to insert a row if the date value of once cell was different from the next cell and so that’s take care of. My problem is I can’t figure out how to write a macro that will insert a row between first and second shift. I formatted each time to general so it’s a decimal. Second shift begins at 14:30 or .604167. There are about 8000 rows of information. I need to write a macro that will insert a row if and only if a cell in column E is less than .604167 and the next cell in column E is greater than or equal to .604167. I just can’t get it to work. Can anyone help me?

    Reply

  • Hi,

    I have a similar issue wherein the date has to be filtered for irrespective of the time, can you please guide me how to go on about this?

    Reply