15 minutes early

[Glenn_H]

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

 

[Niek Otten]

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

|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

 

[Glenn_H]

Ok.

Lets say cells B5 (appointment scheduled time) and C5 (patient arrival time)
are formatted for time.

How would I get cell M5 to return a "Yes" or "No" depending on if the
patient arrived 15 minutes early?

BTW
Thank you for the quuick response.

Glenn

 

[Bob I]

=IF(C5<(B5-0.0104),"Yes","No")

Ok.

Lets say cells B5 (appointment scheduled time) and C5 (patient arrival time)
are formatted for time.

How would I get cell M5 to return a "Yes" or "No" depending on if the
patient arrived 15 minutes early?

BTW
Thank you for the quuick response.

Glenn

:

 

[David Biddulph]

Or may be clearer if expressed as
=IF(C5<(B5-TIME(0,15,0)),"Yes","No")

 

[Glenn_H]

Thank you "Bob I".

"Niek Otten" unfortunately not all end-users are motivated when it comes to
data entry.

So I converted Bob I's
=IF(C5<(B5-0.0104),"Yes","No")

to go with the custom cell formatting of
#":"00

So they don't need to enter the colon.

End formula the Calculates 15 minutes early without convertion back to time
=IF((INT(C5/100)/24)+((C5-(INT(C5/100)*100))/1440)<((INT(B5/100)/24)+((B5-(INT(B5/100)*100))/1440)-0.0104),"Yes", "No")

Works fine.

Again thanks to all.


Glenn