May 24 2022 11:22 AM - edited May 24 2022 01:17 PM
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".
May 24 2022 11:26 AM - edited May 24 2022 01:09 PM
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
May 24 2022 12:47 PM
As variant you could try to calculate on source data like
=SUMPRODUCT(
(checkinTimeRange > appointmentTimeRange)*
( MOD( checkinTimeRange - appointmentTimeRange,1) >
TIME(0,3,0) ) )
May 24 2022 12:50 PM
@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... :)
May 24 2022 12:58 PM
May 24 2022 01:07 PM
SolutionMay 24 2022 01:08 PM
May 24 2022 01:10 PM
Yes, that was a typo. I have corrected my previous reply.
May 24 2022 01:07 PM
Solution