creating an aging report
Hi Robert,
Sure, be glad to. Give me a few. I'm redesigning the entry worksheet,
mostly to get rid of a lot of unecessary fat. A digitally appropriate New
Year's resolution, don't you think?
Peter
"Robert" wrote:
Peter,
Would you mind sharing your pivot table and entry worksheet I am trying to
do this also.
Thanks
Robert
"PSikes" wrote:
"Ron Coderre" wrote:
See if this example is gets you pointed in the right direction:
A1: DueDate
B1: InvAmt
C1: 30_Days
D1: 60_Days
E1: 90_Days
F1: Over_90
A2: (some date)
B2: (an invoice amount)
C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"")
D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"")
E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"")
F2: =IF(TRUNC(($A2-TODAY())/30)=3,$B2,"")
(copy the formulas in C2 thru F2 down as far as you need)
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Pro
"PSikes" wrote:
Hi,
I'm trying to create an Accounts Receivable "aging report", using Excel
2003. I've got a field (a date field), that provides the date of invoice.
I'd like then to add 30, 60, or 90 days from that date, and call that the
"Due Date" for payment in another column.
Thanks,
Peter
Excellent!
Thanks Ron. If I set up my entry worksheet, I can create a little Pivot
table that'll key off of all 3 (30, 60, 90) categories -- just like the more
expensive accounting programs do.
Thanks much,
Peter
|