If and or - so close..

[Melimob]

Hi all

I am so close yet so far with this.. looking for some help please.

Here's my formula
IF(AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"JOHN00",AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"JOHN20",AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"TYCO22",AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"TYCO24")))),"Pre-Process",IF([@[Booking Date]]>=$AH$1,IF(AND([@[Booking Date]]>=$AH$1,AND(LEN([@[ARN-CDF10]]=9),LEFT([@[ARN-CDF10]],2)="TX",[@[ARN-CDF10]]<>"TX0000000",RIGHT([@[ARN-CDF10]],1)<>"/")),"OK","Error"),"Pre-Process"))

I want to add in an 'or' where the ARN-CDF10 can start with either TX, ME or GT. I got this to work but also need it to still always be 9 in length to be 'OK' and this is what I miss?

Any help would be appreciated please.

Many thanks
Melissa

 

[Yongle]

Untested...

Replace

LEFT([@[ARN-CDF10]],2)="TX"

With

OR(LEFT([@[ARN-CDF10]],2)="TX",LEFT([@[ARN-CDF10]],2)="ME",LEFT([@[ARN-CDF10]],2)="GT")

Perhaps...
IF(AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"JOHN00",AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"JOHN20",AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"TYCO22",AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"TYCO24")))),"Pre-Process",IF([@[Booking Date]=]=>=$AH$1,IF(AND([@[Booking Date]=]=>=$AH$1,AND(LEN([@[ARN-CDF10]]=9),OR(LEFT([@[ARN-CDF10]],2)="TX",LEFT([@[ARN-CDF10]],2)="ME",LEFT([@[ARN-CDF10]],2)="GT"),[@[ARN-CDF10]]<>"TX0000000",RIGHT([@[ARN-CDF10]],1)<>"/")),"OK","Error"),"Pre-Process"))

 

[Melimob]

Thanks Yongle..

I tried this earlier. It works to say TX/GT/ME is 'OK' but doesn't test the length being 9 in each case and thats what I need it to also do

Untested...

Replace

LEFT([@[ARN-CDF10]],2)="TX"

With

OR(LEFT([@[ARN-CDF10]],2)="TX",LEFT([@[ARN-CDF10]],2)="ME",LEFT([@[ARN-CDF10]],2)="GT")

Perhaps...
IF(AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"JOHN00",AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"JOHN20",AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"TYCO22",AND([@[Booking Date]]<=$AH$1,[@[ Client ID ]]<>"TYCO24")))),"Pre-Process",IF([@[Booking Date]=]=>=$AH$1,IF(AND([@[Booking Date]=]=>=$AH$1,AND(LEN([@[ARN-CDF10]]=9),OR(LEFT([@[ARN-CDF10]],2)="TX",LEFT([@[ARN-CDF10]],2)="ME",LEFT([@[ARN-CDF10]],2)="GT"),[@[ARN-CDF10]]<>"TX0000000",RIGHT([@[ARN-CDF10]],1)<>"/")),"OK","Error"),"Pre-Process"))

 

[Yongle]

Like I said, I did not test your original formula. I did not spot this earlier but...

shouldn't
LEN([@[ARN-CDF10]]=9)
be
LEN([@[ARN-CDF10]])=9

 

[Melimob]

Wow! how did you spot that! thank you so much Yongle! worked a treat!

Like I said, I did not test your original formula. I did not spot this earlier but...

shouldn't
LEN([@[ARN-CDF10]]=9)
be
LEN([@[ARN-CDF10]])=9

 

[Yongle]

It's a dark art...
thanks for the feedback