Excel 2003 Networkdays inside if statement tr

Microsoft Excel 2003 (full product)
August 18, 2010 at 13:45:04
Specs: Windows XP, Intel(R) Core(TM)2 CPU 3.5 GB RAM
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


See More: Excel 2003 Networkdays inside if statement tr

Report •

#1
August 18, 2010 at 16:02:06
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 ?

MIKE

http://www.skeptic.com/


Report •

#2
August 19, 2010 at 04:38:27
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


Report •

#3
August 19, 2010 at 05:13:39
Mike - I am still getting the #NAME? error when I try to evaluate the formula and the "FALSE" answer.

Thanks for trying!

Lu-C


Report •

Related Solutions

#4
August 19, 2010 at 07:59:20
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

http://www.skeptic.com/


Report •

#5
August 19, 2010 at 08:16:31
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

http://www.skeptic.com/


Report •

#6
August 19, 2010 at 08:32:54
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


Report •

#7
August 19, 2010 at 10:56:32
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

http://www.skeptic.com/


Report •

#8
August 19, 2010 at 11:36:38
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

http://www.skeptic.com/


Report •

#9
August 19, 2010 at 12:27:15
Thanks! I'll test this tomorrow - swamped with deadlines today.

I appreciate the help!

Lu-C


Report •

Ask Question