I already have the Analysis Toolpaks installed but Excel will only recognize the NETWORKDAYS formula inside the IF statement as text. Its a longer formula, and it works for all instances except the "PARTIAL SHIPMT & ALLOC MISSING" criteria. If I put " " around the NEWORKDAYS formula it works (and gives me the text version of the formula as the answer, which doesn't give me the information I need, of course) --- otherwise it gives me a "false" answer if no text markers are used.

=IF(EXACT(BL21,"NOT PICKED"),BL21,IF(AND(EXACT(BL21,"PICKED"),BJ21=1),"PICKED BUT TRANS HAS NO ORDER # ",IF(AND(EXACT(BL21,"PICKED"),BJ21>1),NETWORKDAYS(BH21,BJ21,Holidays)-1,IF(AND(EXACT(BL21,"PARTIAL SHIPMT & ALLOC MISSING"),BJ21>1),NETWORKDAYS(BH21,BJ21,Holidays)-1))))

source data in BL21 is "PARTIAL SHIPMT & ALLOC MISSING", BJ21 is 08/17/2010, BH21 is 08/17/2010 and Holidays is the range of dates to be excluded when using NETWORKDAYS. The answer should be 0, but I keep getting "FALSE" and when I step through the formula it gives the #NAME? error unless I put " " around the NETWORKDAYS formula

What is really weird is that I get the correct answer for the BL21 "PICKED" AND BJ21>1 criteria which also uses the NETWORKDAYS formula.

help, please

Lu-C Leisemann

The best I can come up with is in the last =IF() function:

IF(AND(EXACT(BL21,"PARTIAL SHIPMT & ALLOC MISSING"),BJ21>1),NETWORKDAYS(BH21,BJ21,Holidays)-1Can this ever happen:

if PARTIAL SHIPMT & ALLOC MISSING is true, but BJ21=1 ?

MIKE

Yes - it's a download from Global E-Dash and they use 01/01/1900 as the date if the field is blank or has a zero in it. Our computing software (BPCS) treats date fields as numbers only, so we often have a 0 in a date field. And E-Dash changes it to the above date, which excel reads as Serial # 00001

Thanks for your reply - I'll test it and let you know if it helps!

Lu-C

Mike - I am still getting the #NAME? error when I try to evaluate the formula and the "FALSE" answer. Thanks for trying!

Lu-C

What changes did you make? Did you add an additional =IF() function to take care of the

"if PARTIAL SHIPMT & ALLOC MISSING is true, but BJ21=1"

situation?MIKE

Please understand that I can't see your work, so ...... Just a shot in the dark, your

Holidaylist contains all dates, no empty cells?The =EXACT() function is case sensitive, so all your text cells are Upper Case, no lower case letters?

MIKE

Mike - Yes, I added the additional if statement to take care of those cases where BJ21=1 and I have all upper case letters in the text cell (it is filled in by a formula to ensure always being spelled correctly and no spacing or case problems). And, yes, my Holiday list is all dates with no empty cells.

Everything says it should work - but it doesn't in just a few cases, so I'm still working on it.

For some reason I prefer to have all my formulas working all the time (rolls eyes) - and it's so frustrating not being able to figure out WHY it doesn't - I wish Excel had some kind of debugging for formulas - it tells me I have an error - but it doesn't tell me where it's coming from.

It's probably something fairly simple that I just can't "see" -- is there anyway to attach a partial copy of the file or send one in separately?

Again, thanks for all your help!

Lu-C

Have you tried: Tools>Formula auditing>Show Watch window

You don't have a large assortment of answers, so which one's work and which ones fail.You know that ""PICKED" AND BJ21>1" works, but what other one do or don't.

Might give a pointer to where the problem is.MIKE

You might also want to add a =TRIM() around your BL21 fields,

just to make sure there a no stray spaces floating around.=IF(EXACT(TRIM(BL21),"NOT PICKED"),BL21,IF(AND(EXACT(TRIM(BL21),"PICKED"),BJ21=1),"PICKED BUT TRANS HAS NO ORDER #",IF(AND(EXACT(TRIM(BL21),"PICKED"),BJ21>1),NETWORKDAYS(BH21,BJ21,Holidays)-1,IF(AND(EXACT(TRIM(BL21),"PARTIAL SHIPMT & ALLOC MISSING"),BJ21>1),NETWORKDAYS(BH21,BJ21,Holidays)-1))))

MIKE

Thanks! I'll test this tomorrow - swamped with deadlines today. I appreciate the help!

Lu-C

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History