IF and MIN functions

[JN]

Hi,

I've been trying to fix the following formula but can't figure out why.

IF(OR(AG2<0,AE2<="07/29/05"),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))

What I am trying to do here is if the "Amt remained" is less than 0, OR
"Date" is less than 07/29/05, I want colum AH to show 0. Otherwise, apply the
calculations, which splits the amount into 2005 and 2006. However, I can't
make the formula show 0 for the dates that is less than 07/29/05. I am not
sure if it has to do with the MIN().

AE AG AH
AI
Date Amt Remained 2005 Portion
2006 Portion
2 1/22/93 $66,102 $2,246
$63,856
3 7/31/06 $27,349 $11,587
$15,762


Thanks!

 

[Bob Phillips]

Try this

IF(OR(AG2<0,AE2<=--"2005-07-29"),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE(--"2005
-12-31",--"2005-07-29")/NETWORKDAYS(--"2005-07-29",AE2))

or better yet, put the dates in cells and use the cell refrence.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Morrigan]

Try replace "07/29/05" with 38562

ie.
IF(OR(AG2<0,AE2<=38562),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))

Just a reminder, n your formula you also excluded the date of
07/29/05.


Hope this helps.

 

[JN]

Thanks. I used a cell reference at first, but it didn't work. So I typed out
the date in the formula, and it still doesn't work.

 

[JN]

Can I use a cell reference in this formula? That way it's easier to update
the date since I need to change the date on weekly basis.

Or is there any way to approach to this formula?
Thanx

 

[Morrigan]

IF(OR(AG2<0,AE2<=value(<referenc
cell>)),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2))

Can I use a cell reference in this formula? That way it's easier t
update
the date since I need to change the date on weekly basis.

Or is there any way to approach to this formula?
Thanx