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/ no

2:30 AM 2:15 AM Yes
3:25 AM 3:25 AM Yes
6: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

Reply ↓  Report •

✔ Best Answer
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.

MIKE

http://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.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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")

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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 No
8:00 AM 8:25 AM No
9:25 AM 9:50 AM No
Yes
Yes
Yes

message edited by DDiaz71


Reply ↓  Report •

Related Solutions

#4
August 22, 2019 at 07:31:53
✔ Best Answer
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.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

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

Reply ↓  Report •

#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...

Reply ↓  Report •

#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 Green
and 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 Formatting
3) 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 button
7) Select the Fill Tab
8) Select a Green color
9) Click OK
10) Click OK

Second Formula:

1) Select your cell or Range of Cells, should be the same as above
2) On the ribbon click Conditional Formatting
3) 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 button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

Third Formula:

1) Select your cell or Range of Cells, should be the same as above
2) On the ribbon click Conditional Formatting
3) 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 button
7) Select the Fill Tab
8) Select a White color (Do Not Select Clear it won't work.)
9) Click OK
10) Click OK

When 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.

trvlr

Alway appreciate the good words. :-)

MIKE

http://www.skeptic.com/


Reply ↓  Report •

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

Reply ↓  Report •

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


message edited by DerbyDad03


Reply ↓  Report •

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

Reply ↓  Report •

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

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#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?

Reply ↓  Report •

#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


Reply ↓  Report •

#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.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#15
August 23, 2019 at 18:33:18
I hope it came with all the proper amenities...

https://www.youtube.com/watch?v=98l...


message edited by DerbyDad03


Reply ↓  Report •

#16
August 24, 2019 at 00:51:10
So that’s why the cheque didn’t arrive chez-moi. Mon dieu... I didn’t realise it was worth enuff to buy such a well equipped maintained and example of marine equipment... Jonah would have been green with envy...

Incidentally I think that vendor is related to chap who created the Edsel...


Reply ↓  Report •

Ask Question