Hi Glenn,,

My advise is always not to try to imitate Excel's Date and Time calculations, but to use Excel's intrinsic functions and
calculation methods.
If you insist on not having to type the ":", then use an intermediate cell which converts B5 and C5 to "real" Excel times and use
those in your formulas.

=TIMEVALUE(TEXT(B5,"#"":""00")), format as Time

I do not understand your examples, as the first one returns "Yes" while the patient was *not* 15 minutes early.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Glenn_H" wrote in message ...
|I am trying to get a return value of "Yes" or "No", pertaining to if a
| patient has arrived 15 minutes early for an appointment.
|
| Cell B5 has the appointment scheduled time
| Cell c5 has the time the patient actually arrived for the appointment
|
| Both B5 and C5 have custom formatting = #":"00, so the end user can enter
| time without the colon, and still have the spreadsheet calculate.
|
|
| Cell M5 contains the following formula:
| =IF((60*(24*(IF((((INT(B7/100)/24)+((B7-(INT(B7/100)*100))/1440))<(INT(C7/100)/24)+((C7-(INT(C7/100)*100))/1440)),
| ((INT(C7/100)/24)+((C7-(INT(C7/100)*100))/1440)-1+(INT(B7/100)/24)+((B7-(INT(B7/100)*100))/1440)),
| ((INT(C7/100)/24)+((C7-(INT(C7/100)*100))/1440)-((INT(B7/100)/24)+((B7-(INT(B7/100)*100))/1440)))))))<(15),"Yes","No")
|
|
| The M5 formula returns a "Yes" as soon as any time is entered in b5.
| The M5 formula does not change to "No" once a time is entered in C5, if B5
| is earlier than 1200.
|
| B5 C5 M5
| 15:25 15:30 Yes
| 10:40 10:57 Yes
|
| I hope this is enough information.
|
| Any assistance is appreciated.
|
|
| Glenn