Aging Report

**relats** · 03-07-2011, 03:36 AM

Hi every one... Can you help me in making formula of my Aging Report. I attached already the file for editing. Any kind of effort is very much appreciated. Thank you so much. More power to this site..

**Jamesr571** · 03-07-2011, 04:53 AM

could you be more specific in what you need so we can input the correct formula

**relats** · 03-07-2011, 10:02 AM

Actually I want the amount will be aged per column according to 0-30 days, 31-60 days 61-90 and so forth. Additionally, due amount beyond 365 days will be aged per month like 13-80 months and so forth. Furthermore, number days are already calculated in column F.

Herewith re-attached file as sample. I put the amount in column according to aging manually. I just want to make it in formula so that it would hugely lessen my manual work. Thank you so much my friend.

**Cutter** · 03-07-2011, 10:59 AM

You can put this in G4 and copy to G13 and drag over to col J

=IF(AND($F4>(COLUMNS($G:G)-1)*30,$F4<COLUMNS($G:G)*30+1),$E4,"")

In K4 put this and drag over to M4 then copy down to row 13

=IF(AND($F4>LEFT(K$3,3)-1,$F4<RIGHT(K$3,3)+1),$E4,"")

I'm leaving the last 3 columns (months) to you so you can decide what # of days to assign. ie how many days for 18 months?

In N4 and copied down you would have:

=IF(AND($E$3>=EDATE($C4,12),$E$3<=EDATE($C4,18)),$E4,"")

In O4 and copied down

=IF(AND($E$3>EDATE($C4,18),$E$3<=EDATE($C4,24)),$E4,"")

and in P4 and copied down

=IF($E$3>EDATE($C4,24),$E4,"")

**relats** · 03-08-2011, 03:50 AM

Oh what a wonderful endeavor you had been to me. My problem is already solved 99.9% but it is actually enough for me. But if you need a revisions regarding months this is it 18months = 546days, 24months =730 days and 24months =731 days and above. Thank you for the sharing knowledge with my sincere appreciation for your efforts. May God bless this site and especially to you.