|
IF and MIN functions
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! |
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) "JN" wrote in message ... 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! |
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 Wrote: 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! -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388067 |
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. "Bob Phillips" wrote: 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) "JN" wrote in message ... 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! |
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" wrote: 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 Wrote: 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! -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388067 |
IF(OR(AG2<0,AE2<=value(<reference cell)),0,AG2*NETWORKDAYS(MIN(AE2,DATEVALUE("12/31/05")),"07/29/05")/NETWORKDAYS("07/29/05",AE2)) JN Wrote:[color=blue] 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" wrote: 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. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388067 |
| All times are GMT +1. The time now is 02:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com