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.
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?
Hi,
Please upload your file to SkyDrive and share the link of the uploaded file here.
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?
Hi,
What is your exact question. Upload your file to SkyDrive and share the link of the uploaded file here.