Late, on time within parameters

[Techguy_783]

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.

Thank you in advance.

 

[Canapone]

Hi,

	A	B	C	D
2	15:00	15:45	16:00	Late
3	13:00	13:25	15:00	On time
4	12:00	11:50	12:50	On time

<tbody>
</tbody>

Foglio1​

Worksheet Formulas Cell Formula 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]

Welcome to the forum.

Try

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

 

[Techguy_783]

Worksheet Formulas Cell Formula 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]

Hi,

a first solution could be:

	A	B	C	D
2	15:00	15:45	16:00	Late
3	13:00	13:25	15:00	On time
4	12:00	11:50	12:50	On time

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Foglio1​

Worksheet Formulas Cell Formula 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")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[gaz_chops]

Or

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

 

[Techguy_783]

Brilliant. Thank you very much for your help!