Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combined Statements
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(F3E3,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. -- Thanks!! Don |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combined Statements
One way:
=IF(ABS(MOD(F2-E2,1))<=15/1440, MOD(H2-E2,1),MOD(H2-G2,1)) In article , Don wrote: 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(F3E3,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort by Last Name when name is combined | Excel Worksheet Functions | |||
Conditional formatting combined with multiple IF statements | Excel Worksheet Functions | |||
Combined Cells | Excel Worksheet Functions | |||
vlookup and match combined? | Excel Worksheet Functions | |||
Combined IF statement... | Excel Worksheet Functions |