Help with Aging Report

[KimberlyL]

Hello,

I'm trying to create an A/P aging report in Excel. I cannot seem to create a complete formula using dates. I want to plug in invoice dates and amounts and have "Total" cells for all invoices between 31-60 days, 62-90, etc, from the current date. I would like this to be a"living" formula that updates when the workbook is opened. Hours have been spent on this and I'm sure this is easy...just not for me ! Thank you for any assistance you can offer. -Kimberly

[mangesh_yadav]

If you could give some sample data and expected answers, someone would be able to help you.

Mangesh

[KimberlyL]

Today is May 25, 2005

Column A has invoice numbers: 11233 , 11234 , 11235 , 11236
Column B has invoices dates: 3/24/05 , 2/24/05 , 1/24/05 , 12/24/04
Column C has invoice amounts: $100.00, $200.00 , $300.00 , $400.00

Row 120 has titles: 31 - 60 , 61 - 90 , 91 - 120 , 120+

Row 121 totals: $100.00 , $200.00, $300.00 , $400.00


Row 121 has totals based on the invoice date and the age of the invoice in reference to todays' date.

What I want to achieve is that if Column B has a date (s) that is 31 - 60 days older then todays' date, the corresponding amount (s) in column C will be added together and the total would appear in Row 121 under the 31 - 60 title. And so on with the 61-90, 91-120 and 120+.

This is hard to explain, I hope this helps. Thanks - Kimberly

[mangesh_yadav]

Do the following:
I entered your sample data in columns A to C, and rows 1 to 4.
Enter todays date in cell G1.
In column D, next to the values in coulumns A, B, & C, enter the following formula:
=$G$1-B1
and drag down for all the rows in column A. This is the number of days from the invoice date.

Enter the following figures in cells A118 to D118:
31, 61, 91, 121
And in A119 to D119
60, 90, 120, (last cell should be blank)

in row 120 cell A120 enter formula:
=A118&" - " &A119
and drag to your right till column D

In row 121, cell A121 enter formula:
=SUMPRODUCT($C$1:$C$4,--($D$1:$D$4>=A118),--($D$1:$D$4<=A119))
and dragto copy to your right till D121

That should give you the required result

Mangesh

[KimberlyL]

Thank you, thank you, thank you! My life (at work) is now so much easier! -Kimberly