aging formulas
 

I am using Excel to track Accounts Payable for a small company. Column A is
the $ amount of the invoice, Column B is the date of the invoice, Column C is
the due date and columns D,E,F,G....are aging (i.e. 1-30, 31-60, 61-90,
91-120).

Is there a function that will age and move the $$ to the correct column?



Thanks,,,,,,
Susan

One way:

Put this in D2 and copy it across to G2

=$A2*(ROUNDUP((TODAY()-$C2)/30,0)=COLUMN(A$1))

"Susan" wrote:

I am using Excel to track Accounts Payable for a small company. Column A is
the $ amount of the invoice, Column B is the date of the invoice, Column C is
the due date and columns D,E,F,G....are aging (i.e. 1-30, 31-60, 61-90,
91-120).

Is there a function that will age and move the $$ to the correct column?



Thanks,,,,,,
Susan

Add this formula to D2 and copy across

=IF(AND($C2<TODAY(),MATCH(TODAY()-$C2,{0,30,60,90,120,150,180,210,240,270,30
0,330,360})=COLUMN(A$1)),$A2,"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Susan" wrote in message
...
I am using Excel to track Accounts Payable for a small company. Column A
is
the $ amount of the invoice, Column B is the date of the invoice, Column C
is
the due date and columns D,E,F,G....are aging (i.e. 1-30, 31-60, 61-90,
91-120).

Is there a function that will age and move the $$ to the correct column?



Thanks,,,,,,
Susan