If function
 

I'm trying to set up a worksheet to age my accounts payable. I want to
determine 30 days, 60 days, 90 days etc.

My formula is:

=if((today()-b2)30,"30 days overdue")

Where b2 is the cell w/ my invoice date in it, and "30 days overdue" appears
if the test is true. However, I need to insert tests for 60, 90, and 120
days as well. My help index talks about nested if functions, and even gives
an example, but when I try to type it that way, it just gives me an error
message! Help!!

TIA

To nest your IFs you would use the following formula:
=IF(TODAY()-B2120,"120 days overdue",IF(TODAY()-B290,"90 days over
due",IF(TODAY()-B260,"60 days overdue",IF(TODAY()-B230,"30 days
overdue","not overdue"))))

An easier way if you only want to see over due in 30 day increments you
could use the following formula:
=IF(TODAY()-B2=30,IF(MOD(TODAY()-B2,30)=0,TODAY()-B2&" days
overdue","overdue but not on 30 day increments."),"not overdue.")

The second one will work for 30,60,90,120, 150, 180, ...

Ryan

"BetsyW" wrote:

I'm trying to set up a worksheet to age my accounts payable. I want to
determine 30 days, 60 days, 90 days etc.

My formula is:

=if((today()-b2)30,"30 days overdue")

Where b2 is the cell w/ my invoice date in it, and "30 days overdue" appears
if the test is true. However, I need to insert tests for 60, 90, and 120
days as well. My help index talks about nested if functions, and even gives
an example, but when I try to type it that way, it just gives me an error
message! Help!!

TIA

Hi Betsym

Try this instead of nested "If's"

=IF(TODAY()-B2<30,0,INDEX({30,60,90,120},1,MATCH(TODAY()-B2,{30,60,90,120},1)))&"
Days Overdue"

HTH

Alan P.


"BetsyW" wrote in message
...
I'm trying to set up a worksheet to age my accounts payable. I want to
determine 30 days, 60 days, 90 days etc.

My formula is:

=if((today()-b2)30,"30 days overdue")

Where b2 is the cell w/ my invoice date in it, and "30 days overdue"
appears
if the test is true. However, I need to insert tests for 60, 90, and 120
days as well. My help index talks about nested if functions, and even
gives
an example, but when I try to type it that way, it just gives me an error
message! Help!!

TIA

Betsy

One mo

=IF(TODAY()-B2<30,"", INT((TODAY()-B2)/30)*30&" days overdue.")

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"BetsyW" skrev i en meddelelse
...
I'm trying to set up a worksheet to age my accounts payable. I want to
determine 30 days, 60 days, 90 days etc.

My formula is:

=if((today()-b2)30,"30 days overdue")

Where b2 is the cell w/ my invoice date in it, and "30 days overdue"
appears
if the test is true. However, I need to insert tests for 60, 90, and 120
days as well. My help index talks about nested if functions, and even
gives
an example, but when I try to type it that way, it just gives me an error
message! Help!!

TIA