creating an aging report

[PSikes]

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

 

[Peo Sjoblom]

Excel dates are just numbers so if your invoice is in a date format that
excel recognizes just add

=A1+30
=A1+60


and do on where A1 holds the date

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Ron Coderre]

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]

Excel dates are just numbers so if your invoice is in a date format that
excel recognizes just add

=A1+30
=A1+60


and do on where A1 holds the date

--
Regards,

Peo Sjoblom

(No private emails please)




Dang, that was easy. Thanks Peo.

Peter

 

[PSikes]

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

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

 

[Robert]

Peter,

Would you mind sharing your pivot table and entry worksheet> I am trying to
do this also.

Thanks

Robert

 

[PSikes]

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]

It certainly would.....

Robert

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

 

[PSikes]

Ron,

I've had a chance to try out your sample. If I understand the script
correctly, I'll not only get a clear snapshot of overdue collections within
the 30,60,and 90 day timeframes, but I can total them up too! For some
reason, the formula doesn't seem to work over the 2005-2006 window though.
If I date an Due date back in November 1 2005 (not so unusual in today's
environment), for instance -- using the TODAY date in the formula. The
invoice amount doesn't show up in the 60 day window that I'd expect to see
it. Did I do something wrong?

Peter

 

[PSikes]

Robert,

Waiting an answer on my last post. The program that Ron suggested works,
but apparently not over the span of a year.
Peter