If function

[BetsyW]

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

 

[Ryan]

To nest your IFs you would use the following formula:
=IF(TODAY()-B2>120,"120 days overdue",IF(TODAY()-B2>90,"90 days over
due",IF(TODAY()-B2>60,"60 days overdue",IF(TODAY()-B2>30,"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

 

[Alan Perkins]

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.

 

[Leo Heuser]

Betsy

One more:

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