# Solved How do I write a formula to determine if on time

August 21, 2019 at 12:11:41
Specs: Windows 10
 I don't want to keep typing yes or no so I need a formula to determine if Actual time (B2) is on time ( + or - 10 minutes cell C2) compared to Scheduled time (A2). I need it to say Yes or No and account for earlier than scheduled time.Scheduled Arrival Actual Arrival On Time yes/ no2:30 AM 2:15 AM Yes3:25 AM 3:25 AM Yes6:00 AM 6:15 AM No ****Driver must arrive at stop within 10 minutes of scheduled time to be considered on-time. Any arrival that is more than 10 minutes past the scheduled arrival time is considered late. message edited by DDiaz71

See More: How do I write a formula to determine if on time

August 22, 2019 at 07:31:53
 An OR() statement should solve that problem.Something like:=IF(OR(A2="",B2=""),"",IF(B2<=(A2+TIME(,10,)),"Yes","No"))If either A2 or B2 are blank, then you get no message.MIKEhttp://www.skeptic.com/

#1
August 21, 2019 at 16:45:27
 Try something like this:=IF(B2<=(A2+0.00694),"Yes","No")See how it works for you.MIKEhttp://www.skeptic.com/

#2
August 21, 2019 at 16:48:58
 Here is another way of doing the same thing using the TIME() function.=IF(B2<=(A2+TIME(,10,)),"Yes","No")MIKEhttp://www.skeptic.com/

#3
August 22, 2019 at 07:19:40
 Both formulas worked, thank you. What would I add to formula to leave cell blank if no data or incomplete data was present in B2? Currently It defaults to yes.Thank you again.7:00 AM 7:25 AM No8:00 AM 8:25 AM No9:25 AM 9:50 AM No Yes Yes Yesmessage edited by DDiaz71

Related Solutions

#4
August 22, 2019 at 07:31:53
 An OR() statement should solve that problem.Something like:=IF(OR(A2="",B2=""),"",IF(B2<=(A2+TIME(,10,)),"Yes","No"))If either A2 or B2 are blank, then you get no message.MIKEhttp://www.skeptic.com/

#5
August 22, 2019 at 07:48:36
 Wow you are great. That was it. Thank you so much.

#6
August 22, 2019 at 08:29:09
 There are a group Excel gurus here who doth do kwite amazing things when asked to help: and phrom which one can learn so much...

#7
August 22, 2019 at 09:15:15
 Just a suggestion, but if you want something a bit more colorful,you could use Conditional Formatting to color the Yes time Greenand the No time Red or any two colors you wish.If your interested, then you will need three formulas:First Formula:1) Select your cell or Range of Cells, A2 - B50 (Change to suit needs)2) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =\$B2<=(\$A2+TIME(,10,))6) Click on the Format button7) Select the Fill Tab8) Select a Green color9) Click OK10) Click OKSecond Formula:1) Select your cell or Range of Cells, should be the same as above2) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =\$B2>=(\$A2+TIME(,10,))6) Click on the Format button7) Select the Fill Tab8) Select a Red color9) Click OK10) Click OKThird Formula:1) Select your cell or Range of Cells, should be the same as above2) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =OR(ISBLANK(\$A2),ISBLANK(\$B2))6) Click on the Format button7) Select the Fill Tab8) Select a White color (Do Not Select Clear it won't work.)9) Click OK10) Click OKWhen editing a Conditional Format formula, Excel will interpret the Arrow Keys as an attempt to point to a cell. This will of course change the formula. Hitting the Escape Key will return you to the original formula.See how that works for you.trvlrAlway appreciate the good words. :-)MIKEhttp://www.skeptic.com/

#8
August 22, 2019 at 09:37:57
 @ "mmc" - just make sure your cheque is in the mail... :-))

#9
August 22, 2019 at 12:02:12
 I'll save Mike the postage. Here's your check.√ message edited by DerbyDad03

#10
August 22, 2019 at 13:17:16
 Purrfekt... if not more...

#11
August 22, 2019 at 14:39:58
 Been waiting a long time for that check, now if only my boat would come in. :-)MIKEhttp://www.skeptic.com/

#12
August 22, 2019 at 14:47:27
 Have you checked at the correct harbour to see if your boat arrived at the same time your cheque arrived; but is held in customs, pending payment import duties, sales tax, and handling charges?

#13
August 23, 2019 at 05:30:10
 Use the check to float a loan.Boat...float...get it?(permission to groan granted)message edited by DerbyDad03

#14
August 23, 2019 at 09:50:54
 Took DerbyDad's advice and with my BIG check bought this boat:https://lisanneharris.files.wordpre...Not sure if I should trust a guy with a patch on his eye, a peg leg and a mangy parrot on his shoulder,but he assured me it was sea worthy.MIKEhttp://www.skeptic.com/