Forum Discussion
Solved
Trying to work with time criteria
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...
- 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.
As variant you could try to calculate on source data like
=SUMPRODUCT(
(checkinTimeRange > appointmentTimeRange)*
( MOD( checkinTimeRange - appointmentTimeRange,1) >
TIME(0,3,0) ) )- 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.