Aging Report

[JoeM]

1) DateRecvd: E2 (ex 7/19/07)
2) DateClosed: F2 (ex 10/16/07)
3) Days (should close with in # of days, Example: 60) H2
4) DueDate: I2 (ex 9/19/07)
5) TurnAroundTime: DateClosed - DateRecvd (example:60) H2
6) # of days out of compliance (example 29) L2
Question:
A) If date closed is null then calculate now() - date recvd otherwise give
me dateclosed - daterecvd, is this possible? What formula would I use?
B) Take 6 & do buckets in a pivottable of 30, 60, 90 and 120 or greater? Is
this possible and are there any examples that can be downloaded?
Any help is greatly appreciated.
Joe

 

[Fred Smith]

A: =if(f2="",now()-e2,f2-e2)
B: If it was me, I would just add columns to your data which calculate the
overdue periods. Then the pivot table will be easy.

As an aside, I would use today() rather than now() to avoid fractional
values.

Regards,
Fred

 

[Luke M]

A)
Assuming F2 might contain text to indicate a null...
=IF(ISNUMBER(F2),F2-E2,TODAY()-E2)

B)
If you put the # of days field into the column/row section, you can right
click on the field item, group, and then group the days into whatever
amounts you'd like.

 

[JoeM]

Wouold anyone know of any sample pivot tables, showing how to do the aging
buckets into a column?
Joe

 

[JoeM]

Hey Guys just wanted to say these formula's help alot. Thanks
Joe

 

[Fred Smith]

In my scenario, you add columns to your data.

Assume you have the age (in days) of the receivable in column H, and the
amount is in C. In additional columns, calculate the bucket:
Current: =if(f2<=30,c2,"")

30: =if(and(f2>30,f2<=60),c2,"")
60: =if(and(f2>60,f2<=90),c2,"")

etc.

Now sum and/or count these columns in your pivot table.

Regards,
Fred

 

[JoeM]

Fred,
Your B sugestion worked too!!! Took me awhile to get there. usually it's the
simple things that kick me everytime.Thanks
Joe

 

[Fred Smith]

Glad to help. Thanks for the feedback.

Regards,
Fred