Tags: TIME

Filter on a column of Date and time values


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.

Derive end date and time from start date and time, office working hours and lunch breaks


Given the following inputs/restrictions, one may want to compute the end date and time of a project:

1. Start date and time of the project; and
2. Official working hours; and
3. Lunch breaks hours

Furthermore, to add to the complication, one may have different National holidays and weekend days i.e. while for some, the weekend days may be Saturday and Sunday, for others it may be Friday and Saturday.

You may refer to both my solutions here

1. Given National holidays and where Saturday and Sunday are the weekend days - Download workbook.  This solution will work in all Excel versions

2. Given National holidays and where Friday and Saturday are the weekend days - Download workbook.  This solution will work only in Excel 2010 and higher versions.