NESTED IFs with Time and dates

My queries all seem to link to using formula with TIME in them, they just never seem to work. The below is quite clunky, i was hoping to make it work then streamline it but struggling with the working bit first......

So the formula in question links into quite a large spreadsheet that has a variety of workstreams on it so the first few If's are basically just ignoring the row if the work function isn't what i'm looking for,

=IF(AU43="IGNORE","Not Eligible for GSOP",IF(AU43<>"ECO","Scheduled Work",IF(AND(AU43="ECO",N43="SUNDAY"),"4 Hours SLA",IF(AND(AU43="ECO",N43="Saturday",Q43>=TIME(9,0,0),Q43<=TIME(16,0,0)),"3 Hours SLA",IF(AND(LEFT(N43,3)={"MON","TUE","WED","THU","FRI"},Q42>=TIME(20,0,0),Q42<=TIME(8,0,0)),"4 Hours SLA","3 Hours SLA")))))
so:

IF(AU43="IGNORE","Not Eligible for GSOP" - works and basically tells the user based on an earlier formula, no compensation needs paid

IF(AU43<>"ECO","Scheduled Work" - works, puts all the work streams i'm not interested in, into another 'pot'

IF(AND(AU43="ECO",N43="SUNDAY"),"4 Hours SLA" - All jobs on a Sunday have 4 hours to complete, regardless of when int he day we received them - works finethen it starts to go wrong.....

IF(AND(AU43="ECO",N43="Saturday",Q43>=TIME(9,0,0),Q43<=TIME(16,0,0)),"3 Hours SLA" - this job type on a Saturday between 9am and 4pm has a 3 hours completeion time, either side of this on a Saturday. I've tried testing this on its own as IF(AND(AU43="ECO",N43="Saturday",Q43>=TIME(9,0,0),Q43<=TIME(16,0,0)),"3 Hours SLA","4hr Standard" and the logic works, it goes wrong in the nested formula though

IF(AND(LEFT(N43,3)={"MON","TUE","WED","THU","FRI"},Q42>=TIME(20,0,0),Q42<=TIME(8,0,0)),"4 Hours SLA","3 Hours SLA" I've only recently added the other days of the week in, originally i just had IF(AND(Q42>=TIME(20,0,0),Q42<=TIME(8,0,0)),"4 Hours SLA","3 Hours SLA" but everything comes out as 3 hours, so basically its defaulting to the false outcome on everything.

Where am i going wrong - am i trying to do too much, is there a different logic to apply. i've spent so long looking at this i'm struggling to take a step back to see a better way of putting it together. Any help much appreciated as i'm just formula blind at the minute :-)

It would be much easier to help if we had the data so please post a small sample file (mot image).

To upload file, click "Go advanced" the scroll down to "Manage Attachments"

Hi,

It looks like you've mixed up the logic. The separate parts of the formula do not match the full formula you've mentioned.
Try this as a alternative:

=IF(AU43="IGNORE","Not Eligible for GSOP",IF(AU43<>"ECO","Scheduled Work",CHOOSE((((LEFT(N43,3)="Sun")*(AU43="ECO"))*1)+(((LEFT(N43,3)="Sat")*(AU43="ECO")*(MEDIAN("9:00","16:00",Q43)=Q43))*2)+(((LEFT(N43,3)="Sat")*(AU43="ECO")*(MEDIAN("9:00","16:00",Q43)<>Q43))*3)+(((LEFT(N43,3)<>"Sun")*(LEFT(N43,3)<>"Sat")*(AU43="ECO")*MEDIAN(Q43,"08:00","20:00")=Q43)*4)+((LEFT(N43,3)<>"Sun")*(LEFT(N43,3)<>"Sat")*(AU43="ECO")*(MEDIAN(Q43,"08:00","20:00")<>Q43)*5),"4 Hrs SLA","3 Hrs SLA","4 Hrs SLA","4 Hrs SLA","3 Hrs SLA")))

It a long one but does the trick!.
Hope this helps.

thank you very much, it works !!!

What a legend :-)