Too Long formula to shorten help

Dear all
I have a excel sheet which has a long invoice list and column M has invoice the date. I like to work out aged debt for each invoice on a cell as "Current","31-60","61-90","91-120","121-150","151-180","181-210","211+".When I have type this fomula, it works up to "151-180" anything after that woulnd work. I tkink the formula is too long.

Is there a way to shorthen this formula or is there a way to create a Public Function so that I dont have to type a long formula?


=IF(TODAY()-M3<=30,"Current ",
IF(AND(TODAY()-M3>=31,TODAY()-M3<=60),"31-60",
IF(AND(TODAY()-M3>=61,TODAY()-M3<=90),"61-90",
IF(AND(TODAY()-M3>=91,TODAY()-M3<=120),"91-120",
IF(AND(TODAY()-M3>=121,TODAY()-M3<=150),"121-150",
IF(AND(TODAY()-M3>=151,TODAY()-M3<=180),"151-180",
IF(AND(TODAY()-M3>=181,TODAY()-M3<=210),"181-210",
IF(TODAY()-M3<=211,"211+”))))))))


thank you very much for your help in advance.

Thank you for your reply Richard Buttrey

I have now uploaded the excell sheet, Colum Q (Aged) has the formula.

kind regards

Originally Posted by Sambo kid

Just as an FYI, in your last part, this... IF(TODAY()-M3<=211,"211+”))))))))
it should be this to work... IF(TODAY()-M3>=211,"211+”))))))))

I seee it has a typo on this line but the problem is excel not excepting the long formula

Originally Posted by AZ-XL

Maybe this one may shorten
=VLOOKUP(TODAY()-M3,{0,"Current";31,"31-60";61,"61-90";91,"91-120";121,"121-150";151,"151-180";181,"181-210";211,"211+"},2,1)

thank you very much it looks like this has solved the problem. you are a legend!!

Thank you for all of you

Originally Posted by Sambo kid

Yes, my guess is your version of excel is older but you didn't list what it is. It works fine in mine with the correction. AZ-XL's formula also works for me, have you tried that?

EDIT: got that answer below.

Thank you , yes I have tried and it worked perfectly.