# How to calculate transport with a few conditions

1. ## How to calculate transport with a few conditions

Hi all,

I need some help to fix my formula.

The condition to pay the workers transport fee is:
If it is weekday, we will only pay if they start work before 7am or after 8pm. I.E. to say if they start work at 7am and end at 6pm, we only pay them one way. If they work 7am -8pm, we will pay them 2 ways.

if it is weekend, we will pay them 2 ways.

Currently the formula i wrote is:
IFERROR(IF(WEEKDAY(T7,2)>5,VLOOKUP(\$F\$2,M:Q,3,FALSE),IF(Y7<=(7/24),VLOOKUP(\$F\$2,M:Q,3,FALSE),"-")),"-")

Unfortunately, I cannot get it to work for those cases that did not cross 8pm. Apparently my formula is still paying them before 8pm. can anyone advise?  Register To Reply

2. ## Re: How to calculate transport with a few conditions

Maybe

IFERROR(IF(WEEKDAY(T7,2)>5,VLOOKUP(\$F\$2,M:Q,3,FALSE),IF(AND(Y7<=(7/24), Z7>= 20/24),VLOOKUP(\$F\$2,M:Q,3,FALSE),"-")),"-")
where Z7 is the end time cell
?  Register To Reply

3. ## Re: How to calculate transport with a few conditions

IFERROR(IF(WEEKDAY(T7,2)>5,VLOOKUP(\$F\$2,M:Q,3,FALSE),IF(OR(W7<=(7/24),X7>=(20/24)),VLOOKUP(\$F\$2,M:Q,3,FALSE),"-")),"-")

where w7 is start time, and x7 is end time. I dont think it should be using "AND". so i changed to "OR".

but even so, it still cannot work =(  Register To Reply