Aging Report

  • Thread starter Thread starter JoeM
  • Start date Start date
J

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
 
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
 
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.
 
Wouold anyone know of any sample pivot tables, showing how to do the aging
buckets into a column?
Joe
 
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,"")
Click to expand...
etc.

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

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