15 minutes early
 

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

15 minutes early
 

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

15 minutes early
 

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

"Niek Otten" wrote:

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

15 minutes early
 

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

Glenn_H wrote:

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

"Niek Otten" wrote:


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

15 minutes early
 

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

"Bob I" wrote in message
...

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

Glenn_H wrote:

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

"Niek Otten" wrote:


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