Hi,

I have this formula in Excel, but it only works in some cases, not all. I
can't figure out why, maybe someone can help me with this.

IF(ISERROR(IF(OR(AG2<0,AE2<DATEVALUE("01/01/05"),AE2<="07/01/05"),0,AG2*NETWORKDAYS($B$265,MIN(AE2,DATEVALUE("1 2/31/05")))/NETWORKDAYS("07/29/05",AE2))),0,IF(OR(AG2<0,AE2<DATEVALUE("1/1/05"),AE2<="07/29/05"),0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2)))

Thanx!

When do you get errors?

"JN" wrote:

Hi,

I have this formula in Excel, but it only works in some cases, not all. I
can't figure out why, maybe someone can help me with this.

IF(ISERROR(IF(OR(AG2<0,AE2<DATEVALUE("01/01/05"),AE2<="07/01/05"),0,AG2*NETWORKDAYS($B$265,MIN(AE2,DATEVALUE("1 2/31/05")))/NETWORKDAYS("07/29/05",AE2))),0,IF(OR(AG2<0,AE2<DATEVALUE("1/1/05"),AE2<="07/29/05"),0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2)))

Thanx!

The formula doesn't work for years 19XX -2003. Based on the formula, it
should be 0. However, they are showing up with negative numbers.

JN


"bj" wrote:

When do you get errors?

"JN" wrote:

Hi,

I have this formula in Excel, but it only works in some cases, not all. I
can't figure out why, maybe someone can help me with this.

IF(ISERROR(IF(OR(AG2<0,AE2<DATEVALUE("01/01/05"),AE2<="07/01/05"),0,AG2*NETWORKDAYS($B$265,MIN(AE2,DATEVALUE("1 2/31/05")))/NETWORKDAYS("07/29/05",AE2))),0,IF(OR(AG2<0,AE2<DATEVALUE("1/1/05"),AE2<="07/29/05"),0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2)))

Thanx!

I don't know what is happening,
but you could try
=if(AE2=datevalue("7/29/05"),0,max(0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2)))
instead
"JN" wrote:

The formula doesn't work for years 19XX -2003. Based on the formula, it
should be 0. However, they are showing up with negative numbers.

JN


"bj" wrote:

When do you get errors?

"JN" wrote:

Hi,

I have this formula in Excel, but it only works in some cases, not all. I
can't figure out why, maybe someone can help me with this.

IF(ISERROR(IF(OR(AG2<0,AE2<DATEVALUE("01/01/05"),AE2<="07/01/05"),0,AG2*NETWORKDAYS($B$265,MIN(AE2,DATEVALUE("1 2/31/05")))/NETWORKDAYS("07/29/05",AE2))),0,IF(OR(AG2<0,AE2<DATEVALUE("1/1/05"),AE2<="07/29/05"),0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2)))

Thanx!