Combined Statements

[Guest]

I am trying to combine formulas for this data that says:

If a truck arrives up to 15 minutes after his appointment time, then
subtract the load end from appt time.
I have that formula from a previous post
=IF(MOD(F2-E2,1)<=15/1440,MOD(H2-E2,1)) and it works perfectly. My result
should be 1:30:00 for truck 2.

The second scenario is when the truck arrives late or early for his
appointment, then subtract the load end from load start.
I have that formula to be =IF(F3<=E3,H3-G3,IF(F3>E3,H3-G3)) and it would
return 1:00:00 for truck 3, 2:00:00 for truck 4, and 00:45:00 for truck 5.

E F G H
1 appt time arr time load start load end
2 07:00:00 07:10:00 07:30:00 08:30:00
3 08:00:00 10:00:00 11:00:00 12:00:00
4 00:00:00 16:50:00 18:00:00 20:00:00
5 16:30:00 13:00:00 13:15:00 14:00:00


The problem is combining the two logics. I wish to first check that the
truck falls within the 15 minute window. If so, follow the first statement
and stop. If not, then ignore the first statement and perform the second.

Appreciate the help.

 

[JE McGimpsey]

One way:

=IF(ABS(MOD(F2-E2,1))<=15/1440, MOD(H2-E2,1),MOD(H2-G2,1))