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 !
Luke
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.
Celia
Posted by Celia on January 15, 2001 8:12 AM
Correction
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.
celia
