MrExcel Publishing
Your One Stop for Excel Tips & Solutions

aging of account

Posted by Luke on January 13, 2001 5:59 PM

I would like to produce an aging of account using Excel

I would need formulas , or macro to sort and to produce

a 30- 60- 90- 120 listing

Thanks in advance for helping me !


Posted by Celia on January 13, 2001 10:55 PM

Assuming your dates are in column A starting in A2, format a blank column as General, enter the following formula in row 2 and fill down :-
=IF(TODAY()-A1>120,"Over 120",(IF(TODAY()-A1<31,30,IF(TODAY()-A1<61,60,IF(TODAY()-A1<91,90,IF(TODAY()-A1<121,120))))))
Then sort and sub-total to fit your requirements.

Posted by Celia on January 15, 2001 8:12 AM


The cell refs in the formula should, of course, be A2 not A1
Also, if you want to use a date other than today's date for the age analysis, instead of using TODAY() in the formula, put the date you require in a cell and replace TODAY()in the formula with the cell ref that contains the date.
Then when you want to update the aging, just enter a new date in the date cell.