| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting total minutes into hours and minutes in Excel | Excel Worksheet Functions | |||
| Early Login, Last Logout | Excel Worksheet Functions | |||
| converting Days Hours & minutes into just minutes in excel | Excel Discussion (Misc queries) | |||
| how to change a decimal number (minutes) into hours and minutes? | Excel Discussion (Misc queries) | |||
| add column of minutes, show total in hours & minutes | Excel Worksheet Functions | |||
Linear Mode
