# Conditional specific time frame calculation

1. ## Conditional specific time frame calculation

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))),"")  Register To Reply

2. ## Re: Conditional specific time frame calculation

Try

=IFERROR(1/(1/IF(AND(OR(B3="PRG/FLY",B3="FLY1",B3="FLY",B3="PRG/FLY-CO",B3="FLY1-CO",B3="FLY-CO")),IF(INT(J4)=INT(L4),MIN(6/24,MOD(L4,1))-MIN(6/24,MOD(J4,1))+MAX(MOD(L4,1),18/24)-MAX(18/24,MOD(J4,1)),MIN(INT(L4)+6/24,L4)-MAX(INT(J4)+18/24,J4)),"x")),"")  Register To Reply

3. ## Re: Conditional specific time frame calculation

Thank you,

The formula is working fine in T3 after replacing J4 and L4 with J3 and L3.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1