yogi_Count Number Of Cases Started By Specified Shift Times

Google Spreadsheet   Post  #2236

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-04-2017
question by: BenGenAnes

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/_orgPl_Us7A;context-place=forum/docs

Counting the number of cases in a specific time period

Hello all !  I have a database that tracks different operative cases in 3 different shifts in a day.  The shifts start at 7am to 3pm, then 3pm to 11pm then 11pm to 7am the following day.  We record the case, start of the operation and end of the operation in google sheets.  I've been trying to automatically count the number of cases between these shifts to give us an idea of how our hospital is performing.  I tried the formula : =COUNT(FILTER(BH2:BH, BH2:BH >= TIME(7,0,0), BI3:BI < TIME(15,0,0))), substituting the time for the 3 shifts but the total of the number of cases between the shifts does not tally to the total number of cases. Am I doing something wrong ? Help please !

Example:

Column BG Column BH Column BI

Case Start Time End Time

Appendectomy 9:23:00 10:00:00

LSCS 10:55:23 11:31:31

Appendectomy 12:21:30 12:55:00

LSCS 3:10:23 3:45:45

Appendectomy 5:30:25 6:31:31

LSCS 23:50:50 00:23:30