The formula in T3 from the attached sheet is working fine, it is calculating the night working hours for the time frame between 18:00 – 06:00 if it happens to fall in the times values of J3 and L3.
I would like please for the existing formula to return blank IF none of the following criteria values is in B3; which can be selected from the B3 dropdown menu.
"PRG/FLY",B3="FLY1",B3="FLY",B3="PRG/FLY-CO",B3="FLY1-CO",B3="FLY-CO"
Reference to the attached sheet, the expected formula should return please 1:06, but since there is a “TVL” in B3 which not one of the criteria values listed above, it should return blank instead.
I tried the following modification, but unfortunately the modified formula has a problem.
=IFERROR(1/(1/IF(AND(OR(B3="PRG/FLY",B3="FLY1",B3="FLY",B3="PRG/FLY-CO",B3="FLY1-CO",B3="FLY-CO"),INT(L3),MIN(6/24,MOD(L3,1))-MIN(6/24,MOD(J3,1))+MAX(MOD(L3,1),18/24)-MAX(18/24,MOD(J3,1)),MIN(INT(L3)+6/24,L3)-MAX(INT(J3)+18/24,J3))),"")
Bookmarks