|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.