How to calculate transport with a few conditions

[freeman1988]

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?

[ChemistB]

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
?

[freeman1988]

Thanks ChemistB for your input. I adjusted it to :

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 =(