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)-1
Can this ever happen:
if PARTIAL SHIPMT & ALLOC MISSING is true, but BJ21=1 ?
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?
Please understand that I can't see your work, so ...... Just a shot in the dark, your Holiday list 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 - 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.
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))))
Ad Choices