Aging Invoice Calculator - Excel Formula SNAFU

bristolxyz

New Member
Joined
Aug 21, 2003
Messages
5
I have an excel sheet that I want to calculate total of invoices that are 31-60, 61-90 and over 90 days old. I can only get the formula to calculate based on what MONTH it currently is. I have a cell that contains a DATE, what I would like to do is be able to put any date in that cell and the formula calculate the results accordingly based on that date. I will follow this post with an attempt to display the sheet here in teh forum...

Regards
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Using a PivotTable...
Book1
ABCDEFGHIJKLMN
1InvoiceDateAmountAgeSumofAmount
21004A7/15/200310140AgeTotal
31005B9/25/2003156831-6025
42012B10/13/2003255061-9015
5>9010
6GrandTotal50
7
8
Sheet3
 
Upvote 0
That's ALMOST IT! :)
I see how it uses the TODAY in calculating, but I guess what I want to do is take the amount in a cell IF it's 31-60 days old and add it with others that meet the same criteria.

I must be able to change the date that the formul is based on, such as not TODAY but enter any other date and calculate based on that date. That way, I can forcast what the aging report is going to look like on future dates.

Am I confused? :)
 
Upvote 0
bristolxyz said:
That's ALMOST IT! :)
I see how it uses the TODAY in calculating, but I guess what I want to do is take the amount in a cell IF it's 31-60 days old and add it with others that meet the same criteria.

I must be able to change the date that the formul is based on, such as not TODAY but enter any other date and calculate based on that date. That way, I can forcast what the aging report is going to look like on future dates.

Am I confused? :)

Simply replace the TODAY() worksheet function with a reference to a cell containing a date that you want to use in the comparison.
 
Upvote 0
Aging Debtors.xls
ABCDEFGH
1Date:3-Dec-03
2
3Invoice#DateAmountCurrentOver30Over60Over90
418-Jan-03$100.00   $100.00
5231-Mar-03$150.00   $150.00
6330-Jun-03$75.00   $75.00
7415-Aug-03$30.00   $30.00
8522-Aug-03$50.00   $50.00
965-Sep-03$28.00  $28.00 
10729-Sep-03$15.00  $15.00 
11811-Oct-03$26.00 $26.00  
12922-Oct-03$150.00 $150.00  
13104-Nov-03$66.00$66.00   
141125-Nov-03$82.00$82.00   
15121-Dec-03$55.00$55.00   
16
Sheet1


Change the date in cell D1.

D4: =IF($D$1-B4<=30,C4,"")
E4: =IF(AND($D$1-B4>=31,$D$1-B4<=60),C4,"")
F4: =IF(AND($D$1-B4>=61,$D$1-B4<=90),C4,"")
G4: =IF($D$1-B4>90,C4,"")

Copy down.

You may have to tweak the formulas to get exactly what you want.

HTH


Mike
 
Upvote 0
Ekim this is just what I need!! The only problem is I don't know how to get it to adjust the aging if a payment has been applied against the invoice. Hope 10 years later you're still around to help, or someone can :)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top