Len if and

[horizonflame]

Hi

I have set-up the below but the LEN calculation seems wrong as LEN that isn't 15 is matching and doing the workday function.

=IF(AND(LEN($B10=15),$C10="Apple"),WORKDAY($I10,45,Info!$B$18:$B$25),
IF(AND(LEN($B10=15),$C10="Banana"),WORKDAY($I10,45,Info!$B$18:$B$25),$I10))

Can anyone see what is wrong please?

 

[Fluff]

You've got the clsoing bracket in the wrong place, it should be
LEN($B10)=15,$C10

 

[horizonflame]

Thanks Fluff! I'm now getting #VALUE error on the values that match - any idea on what's wrong there?

Thanks

 

[Fluff]

Can you post some sample data?

 

[horizonflame]

[TABLE="width: 327"]
<tbody>[TR]
[TD="width: 128, bgcolor: #D9D9D9"]COLUMN B

[/TD]
[TD="width: 87, bgcolor: #D9D9D9"]COLUMN D
[/TD]
[TD="width: 75, bgcolor: #D9D9D9"]COLUMN I
[/TD]
[TD="width: 146, bgcolor: #D9D9D9"]FORMULA COLUMN
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]777/11215
[/TD]
[TD="bgcolor: #BDD7EE"]Pear
[/TD]
[TD="bgcolor: #BDD7EE"]30/03/2020
[/TD]
[TD="bgcolor: #BDD7EE"]30/03/2020
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]777/11531
[/TD]
[TD="bgcolor: #BDD7EE"]Apple

[/TD]
[TD="bgcolor: #BDD7EE"]30/09/2019
[/TD]
[TD="bgcolor: #BDD7EE"]30/09/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]777/00164
[/TD]
[TD="bgcolor: #BDD7EE"]Melon
[/TD]
[TD="bgcolor: #BDD7EE"]31/07/2019
[/TD]
[TD="bgcolor: #BDD7EE"]31/07/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]777/11392
[/TD]
[TD="bgcolor: #BDD7EE"]Banana
[/TD]
[TD="bgcolor: #BDD7EE"]29/08/2019
[/TD]
[TD="bgcolor: #BDD7EE"]29/08/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]777/11875
[/TD]
[TD="bgcolor: #BDD7EE"]Banana
[/TD]
[TD="bgcolor: #BDD7EE"]30/09/2019
[/TD]
[TD="bgcolor: #BDD7EE"]30/09/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]777/11389
[/TD]
[TD="bgcolor: #BDD7EE"]Apple
[/TD]
[TD="bgcolor: #BDD7EE"]31/07/2019
[/TD]
[TD="bgcolor: #BDD7EE"]31/07/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]777/11390
[/TD]
[TD="bgcolor: #BDD7EE"]Pear
[/TD]
[TD="bgcolor: #BDD7EE"]29/11/2019
[/TD]
[TD="bgcolor: #BDD7EE"]29/11/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]777/11680/04/01
[/TD]
[TD="bgcolor: #BDD7EE"]Apple
[/TD]
[TD="bgcolor: #BDD7EE"]31/03/2020
[/TD]
[TD="bgcolor: #BDD7EE"]#VALUE
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

One thing that springs to mind is the formula is looking at col C, but your sample shows it should be col D.
Also the word Apple on row 10 has a space after it.
Another thing to check is make sure the dates on the info sheet are real dates, rather than text.

 

[horizonflame]

One thing that springs to mind is the formula is looking at col C, but your sample shows it should be col D.
Also the word Apple on row 10 has a space after it.
Another thing to check is make sure the dates on the info sheet are real dates, rather than text.

Thanks Fluff - the issue was the dates ref. Cheers!

 

[Fluff]

You're welcome & thanks for the feedback