SOLVED

Trying to work with time criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-3423009%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20work%20with%20time%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1400149%22%20target%3D%22_blank%22%3E%40mkollman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20might%20be%20easier%20to%20calculate%20the%20difference%20as%20a%20number%20of%20minutes%20instead%20of%20a%20text%20value%20that%20represents%20time.%3C%2FP%3E%0A%3CP%3EUse%20%3D1140*(J3-E3)%20and%20format%20the%20cell%20with%20the%20formula%20as%20General%20or%20as%20Number%20with%200%20decimal%20places.%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%20formulas%20such%20as%20%3DCOUNTIF(%3CEM%3Edifference_range%3C%2FEM%3E%2C%20%22%26gt%3B3%22)%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3423414%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20work%20with%20time%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1400149%22%20target%3D%22_blank%22%3E%40mkollman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20could%20try%20to%20calculate%20on%20source%20data%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUMPRODUCT(%0A%20%20%20(checkinTimeRange%20%26gt%3B%20appointmentTimeRange)*%0A%20%20%20(%20MOD(%20checkinTimeRange%20-%20appointmentTimeRange%2C1)%20%26gt%3B%0A%20%20%20%20%20TIME(0%2C3%2C0)%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3423426%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20work%20with%20time%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20Thanks%20for%20your%20response.%26nbsp%3B%20I%20used%20the%20formula%20you%20noted%2C%20and%20the%20result%20is%2050761039.%26nbsp%3B%20In%20this%20case%2C%20the%20actual%20difference%20between%20appointment%20time%20and%20check%20in%20time%20was%20-4%20minutes%20(the%20patient%20checked%20in%204%20minutes%20early).%26nbsp%3B%20I%20am%20stumped...%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3423491%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20work%20with%20time%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423491%22%20slang%3D%22en-US%22%3ESergei%20-%3CBR%20%2F%3EThank%20you%20for%20your%20response.%20I%20have%20entered%20your%20formula%20as%20follows%3A%20%3DSUMPRODUCT((J3%26gt%3BE3)*(MOD(J3-E3%2C1)%26gt%3BTIME(0%2C3%2C0)))%20The%20result%20produced%20is%20zero%20regardless%20of%20the%20actual%20difference%20between%20appointment%20time%20and%20check%20in%20time.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3423534%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20work%20with%20time%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423534%22%20slang%3D%22en-US%22%3EI%20believe%20your%20response%20has%20led%20me%20to%20the%20answer.%20I%20think%20your%20original%20formula%20had%20a%20typo%2C%20and%20I%20applied%20it%20incorrectly.%20I%20believe%20you%20meant%20to%20suggest%201440*(J3-E3).%20When%20I%20do%20that%20and%20use%20the%20J3-E3%20result%20that%20produced%20result%20in%20decimal%20format%20instead%20of%20the%20result%20in%20hh%3Amm%20format%2C%20it%20works!!%20THANK%20YOU.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3423547%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20work%20with%20time%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1400149%22%20target%3D%22_blank%22%3E%40mkollman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20mean%20this%20one%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20546px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374797i3B39E2E76FAA4157%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3423570%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20work%20with%20time%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3423570%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1400149%22%20target%3D%22_blank%22%3E%40mkollman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20that%20was%20a%20typo.%20I%20have%20corrected%20my%20previous%20reply.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3422964%22%20slang%3D%22en-US%22%3ETrying%20to%20work%20with%20time%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3422964%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20patient%20appointment%20times%20and%20patient%20check%20in%20times%20both%20formatted%20as%20mm%2Fdd%2Fyyyy%20h%3Amm%20A%2FP.%26nbsp%3B%20I%20am%20trying%20to%20analyze%20how%20often%20patients%20are%20checking%20in%20late%2C%20and%20how%20late%20are%20they%20(%26gt%3B3%20minutes%2C%20%26gt%3B5%20minutes%2C%20%26gt%3B7%20minutes%2C%20%26gt%3B10%20minutes).%26nbsp%3B%20There%20are%20two%20columns%20that%20demonstrate%20the%20difference%20between%20appointment%20time%20and%20check%20in%20time.%26nbsp%3B%20The%20first%20simply%20subtracts%20the%20appointment%20time%20from%20the%20check%20in%20time%20and%20produces%20a%20result%20in%20decimal%20format%20with%20early%20check%20in%20represented%20as%20a%20negative.%26nbsp%3B%20This%20column%20of%20results%20is%20formatted%20as%20%22general%22.%26nbsp%3B%20The%20second%20column%20of%20results%20uses%20the%20formula%20%3DIF(J3-E3%26gt%3B%3D0%2CTEXT(J3-E3%2C%22h%3Amm%22)%2CTEXT(ABS(J3-E3)%2C%22-h%3Amm%22))%20and%20displays%20results%20as%20h%3Amm%20but%20the%20column%20is%20formatted%20as%20%22general%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20seem%20to%20write%20an%20equation%20that%20will%20count%20how%20many%20of%20these%20appointments%20meet%20the%20various%20lateness%20criteria%20referenced%20above.%20I%20have%20tried%20various%20if%2C%20then%20and%20countif%20formulas%20and%20keep%20getting%20the%20generic%20%22there%20is%20a%20problem%20with%20this%20formula%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3422964%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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".

7 Replies

@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

@mkollman 

As variant you could try to calculate on source data like

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

@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...

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.
best response confirmed by mkollman (New Contributor)
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.

@mkollman 

I mean this one

image.png

Please check in attached file.

@mkollman 

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