Late, on time within parameters

Techguy_783

New Member
Hi all;

I am attempting to create a spread sheet for deliveries that will automatically apply if its late or on time.
Basicly, there is a delivery time our vehicles have to be there for. However they have a 29 minute windows after the given period booking time to be ''on time''.

So here is what i have. (example)

Column A | Column B | Column C | Column D
Booking Time | DOT IN | DOT OUT | FORMULA
1 15:00 | 15:45 | 16:00 | LATE
2 13:00 | 13:25 | 15:00 | ON TIME
3 12:00 | 11:50 | 12:50 | ON TIME

So these are the kind of results i need.

So row 1, is late due to being over 15:29. The rest are on time due to being within that 29 minutes after delivery. and being early, doesn't really matter.

Could someone please write me a formula that will complete this deeming task.

Last edited:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Canapone

Active Member
Hi,

ABCD
215:0015:4516:00Late
313:0013:2515:00On time
412:0011:5012:50On time

<tbody>
</tbody>
Foglio1

Worksheet Formulas
CellFormula
D2=IF((B2-A2)>TIME(0,29,0),"Late","On time")
D3=IF((B3-A3)>TIME(0,29,0),"Late","On time")
D4=IF((B4-A4)>TIME(0,29,0),"Late","On time")

<tbody>
</tbody>

<tbody>
</tbody>

Hope that helps

gaz_chops

Well-known Member
Welcome to the forum.

Try

=IF(B2>(A2+TIME(0,29,0)),"Late","On Time")

Techguy_783

New Member
Worksheet Formulas
CellFormula
D2=IF((B2-A2)>TIME(0,29,0),"Late","On time")
D3=IF((B3-A3)>TIME(0,29,0),"Late","On time")
D4=IF((B4-A4)>TIME(0,29,0),"Late","On time")

<tbody>
</tbody>

<tbody>
</tbody>

Hope that helps[/QUOTE]

Brilliant, thank you very much. Is there away to corporate a blank option for any that do not have times? as with this formula, regardless of a blank box will come back saying "on time".

Canapone

Active Member
Hi,

a first solution could be:

ABCD
215:0015:4516:00Late
313:0013:2515:00On time
412:0011:5012:50On time

</tbody>
Foglio1

Worksheet Formulas
CellFormula
D2=IF(OR(ISBLANK(A2),ISBLANK(B2)),"",IF((B2-A2)>TIME(0,29,0),"Late","On time"))
D3=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",IF((B3-A3)>TIME(0,29,0),"Late","On time"))
D4=IF(OR(ISBLANK(A4),ISBLANK(B4)),"",IF((B4-A4)>TIME(0,29,0),"Late","On time"))

</tbody>

<tbody>
</tbody>

gaz_chops

Well-known Member
Or

=IF(A2="","",IF(B2>(A2+TIME(0,29,0)),"Late","On Time"))

Techguy_783

New Member
Brilliant. Thank you very much for your help!

Replies
6
Views
462
Replies
1
Views
758
Replies
14
Views
738
Replies
3
Views
2K
Replies
3
Views
244

1,195,659
Messages
6,010,957
Members
441,578
Latest member
brodiej

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back