Create a custom A/R Aging in Excel

[Rich]

Hello, I am new to the group and very thankful for having found it! I
need to figure out how to create and Aging routine in an Excel
worksheet. Basically what I have is this scenario,

I have: CUST_ID, STATE, DATE_OF_SERVICE, and CHARGES.

These are in columns A,B,C,D.

What I need in coluns E, F, G, H, I is a formula in each cell that
will evaluate the DATE_OF_SERVICE and based on the function TODAY()
subtract the DATE_OF_SERVICE from TODAY() and use that result as the
"AGE" of the CHARGES and place the value for CHARGES in the
appropriate "AGE BUCKET" IE. The date of service for one customer is
May 1, 2004 and TODAY() = July 1, 2004 the result of taking
TODAY()-May 1, 2004 is 71 therefore based on the aging buckets below I
would want the charges of $240.00 to show up in the third "BUCKET"
column "G" and the others to simply read $0.00!

Columns E thru I headers read something like this:

E= 0 to 30 Days Old
F= 31-60 Days Old
G= 61-90 Days Old
H= 91-180 Days Old
I= 181 Days and Older.

The first one in column E is basically a simple IF statement similar
to this:

=IF(TODAY()-C2<=30,D2,0))

The last one would be similar to:

=IF(TODAY()-C2>=181,D2,0))

BUT NOW... the tough part, for me anyway, is I need a statement that
says if the result of TODAY()-C2 IS BETWEEN... this is where my skills
lack in Excel. First I hope what I am asking makes sense and Second
if so, can anyone out there help me or give me some pointers! I
appreciate any help I can get and look forward to replying to, and
maybe even helping another member in the future!

Thank you!

Rich

 

[Dion]

Rich - this is not the best way, but may be the simplest,
based on what you already have/know:

=IF(TODAY()-C2>30,IF(TODAY()-C2<60,D2))

This will bucket anything between 30-60 days.

Each bucket would need it's own formula, which is why
this is not the best way (in my opinion). When I do
these things, I use SUMPRODUCT. If you want to go this
route, message back, but the above may be all you need.

Dion

 

[Frank Kabel]

Hi
try for the second 'bucket':
=IF(AND(TODAY()-C2>30,TODAY()-C2<=60),D2,0))

Though this works I would suggest something different to save you
editing all formulas manually. Insert in the heading row (starting in
E1) only the lower boundary of your 'bucket'. e.g.
E1: 0
F1: 31
G1: 61

Now use the following formula in E2:
=IF(AND(TODAY()-$C2>=E$1,TODAY()-$C2<F$1),$D2,0))

and copy this formula to the right (and down)