Trying to work with time criteria

[mkollman]

I have a spreadsheet with patient appointment times and patient check in times both formatted as mm/dd/yyyy h:mm A/P.  I am trying to analyze how often patients are checking in late, and how late are they (>3 minutes, >5 minutes, >7 minutes, >10 minutes).  There are two columns that demonstrate the difference between appointment time and check in time.  The first simply subtracts the appointment time from the check in time and produces a result in decimal format with early check in represented as a negative.  This column of results is formatted as "general".  The second column of results uses the formula =IF(J3-E3>=0,TEXT(J3-E3,"h:mm"),TEXT(ABS(J3-E3),"-h:mm")) and displays results as h:mm but the column is formatted as "general".

I can't seem to write an equation that will count how many of these appointments meet the various lateness criteria referenced above. I have tried various if, then and countif formulas and keep getting the generic "there is a problem with this formula".

[#2]

@mkollman 

It might be easier to calculate the difference as a number of minutes instead of a text value that represents time.

Use =1440*(J3-E3) and format the cell with the formula as General or as Number with 0 decimal places.

You can then use formulas such as =COUNTIF(difference_range, ">3") etc.

Edited to correct mistake

[#3]

@mkollman 

As variant you could try to calculate on source data like

=SUMPRODUCT(
   (checkinTimeRange > appointmentTimeRange)*
   ( MOD( checkinTimeRange - appointmentTimeRange,1) >
     TIME(0,3,0) ) )

[#4]

@Hans Vogelaar   Thanks for your response.  I used the formula you noted, and the result is 50761039.  In this case, the actual difference between appointment time and check in time was -4 minutes (the patient checked in 4 minutes early).  I am stumped... :)

[#5]

Sergei -
Thank you for your response. I have entered your formula as follows: =SUMPRODUCT((J3>E3)*(MOD(J3-E3,1)>TIME(0,3,0))) The result produced is zero regardless of the actual difference between appointment time and check in time.

[#6 · Accepted solution]

I believe your response has led me to the answer. I think your original formula had a typo, and I applied it incorrectly. I believe you meant to suggest 1440*(J3-E3). When I do that and use the J3-E3 result that produced result in decimal format instead of the result in hh:mm format, it works!! THANK YOU.

[#7]

@mkollman 

I mean this one

Please check in attached file.

[#8]

@mkollman 

Yes, that was a typo. I have corrected my previous reply.