Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Aging Formula

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have attached a spreadsheet that shows a summary of an account to date. I want to put in formulas to continue as new charges and payments occur. Thanks in advance - and I hope the upload worked!!

View Answers     

Similar Excel Tutorials

How to Input, Edit, and manage Formulas and Functions in Excel
In this tutorial I am going to introduce how to input, edit and manage excel formulas. To start entering a formula, ...
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...

Helpful Excel Macros

Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel

Similar Topics

Hi the

I have a business that I need to report (and pay) sales tax on. In my spreadsheet I have the following formula:

=SUMIF('Charges & Payments'!H3:H307,A4,'Charges & Payments'!E3:E307)

...where "A4" is the deposit number. This formula collects all the check amounts on the 'Charges & Payments' sheet for a particular deposit number and adds them together. A subsequent cell calculates the tax. Everything works fine.

Now I have a new condition. Some new clients have tax exemption status. On the 'Charges & Payments' sheet I can easily add a column to indicate whether a particular clients' check is taxable or not. However, I do not know how to test for the additional condition within the above formula.

I did come up with the following formula:

=SUMIF('Charges & Payments'!H3:H307,A4,(SUMIF('Charges & Payments'!D3:D307,"'N'",'Charges & Payments'!E3:E307))) test for a new column that could have a "N" in it indicating tax exempt statue. Excel does not like it, although the reason is not clear. I also tried just using an "IF" in place of the inner or outer "SUMIF" and although I was able to get around the error messages, the result was incorrect.

Anyone have an idea?

Thanks - TomR

I have spreadsheet1 (Aging) with names and dates, Names can appear more than once. Spreadsheet2 (Customers) has a list of customers and formulas to calculate aging date. Customers appear only once. I want to match Aging names with Customer list then apply formula to caluculate aging date. Exp:Aging shows Customer Smith with a date of 3/01/09, Customer list shows Smith with a formula of B2+10. Column B is the date col. Answer would be 3/11/09. I have used VLookup. It returns the formula not the result.
Any help for this novice would be appreciated.

I would like a more detailed Aging of Account in Excel in a scenario like this: If I give advance to a customer and he clears his oustanding with supply of goods. Outstanding will be cleared on FIFO basis.

I want an aging analysis of the balance.

Is it possible to create an IF formula that considers two cells in the logical argument? If so, how can it be done? If not, any suggestions?

I'm writing a formula to calculate a due date for payments. It must take into account the first two payment dates (they need to be 11-14 days apart) as well as the number of days in the month. For example - recurring payments can't occur on any day past the 28th of the month because February only has 28 days. Lastly, the due date can't be more than fifteen days away from any of the payments (in either direction - late or early it doesn't matter). It just can't be more than 15 days.

Thanks for your help!

Hi All

I have a spreadsheet that I am currently using to input payments onto and then create a CSV file using VBA which is working well, but I now need to batch the payments together so therefore need to do a summary before saving as a CSV file.

The column headings are *Matter Number, *Payment Amount, *Beneficiary Account Number in columns A:C,

On the new summary page it should just show *Payment Amount and *Beneficiary Account Number, one line for each different account number and the payment amount should be the total for all payments being sent to that account.

Any help would be greatly appreciated.


Hi All

I have a spreadsheet that I am currently using to input payments onto and then create a CSV file using VBA which is working well, but I now need to batch the payments together so therefore need to do a summary before saving as a CSV file.

The column headings are *Matter Number, *Payment Amount, *Beneficiary Account Number in columns A:C,

On the new summary page it should just show *Payment Amount and *Beneficiary Account Number, one line for each different account number and the payment amount should be the total for all payments being sent to that account.

Any help would be greatly appreciated.


Hello everyone,

I would like to use VB to update a sheet labeled "Summary". What I am looking for is if there is a value in row 2,columns A-O of the "Upload" tab: paste this data into row 6, columns A-O of the "Summary" tab. Then, this needs to be repeated for rows 3 of "Upload" and paste into row 7 of the "Summary" tab. Then this process needs to be repeated for all the data on the "Upload" tab.

My data probably will not go past row 30,000: however, I do not know that for sure. If there is an issue with the 65,536 rows in Excel (since we are starting 5 rows lower on the "Summary" tab), we can stop at any row higher than that. If it is easier, I can move the data on the "Upload" tab to begin in row 6. Just let me know if you would like me to do that.

Some examples of formulas I would use (if I cannot get the VB code) a In Cell A6 of Summary: =IF(Upload!A2="","",Upload!A2) In Cell B6 of Summary: =IF(Upload!B2="","",Upload!B2) - repeat this pattern until Column O In Cell A7 of Summary: =IF(Upload!A3="","",Upload!A3) Thanks,

I have seen a number of ticket aging requests, and there was some good information in there, but I have so far not been able to find something that would help with my specific needs.

We have a report which contains some member information, including the date the date received. We would like to have a separate column which would express the ticket aging, in 30, 60, and 90 day intervals.

In this case, however, we do not want the aging to be from the date received, we would like the aging to be from the 1st of the month of the date received. So if the line is dated 2/27/2011, and it is now 3/1/2011, the record should be marked as being 30 days old.

I have attached a spreadsheet which will hopefully explain what I am talking about better.

Additionally, if there is a way that the entore row can change the background color based on this aging value, that would be great as well.

Thank you.

Hi all,

I get a report every month with about 3000 entries which shows the progess of projects carried out and I have to work out how much money is owed from the % achieved on each project. Different Projects earn different amounts of money, the layout of the sheet is as follows (I have tried attaching part of the sheet with no luck):

Col A & B is first & last Name
Col C Is the Start Date
Col D Is the Planned end date
Col E is % complete
Col F is the Project Number being carried out
Col G is the Project Assessor

This is the information I recieve, what I have to manually input and what I would like to do is as follows:

Col H is the money owed for the full project (What I want is a lookup table to detirmine how much is to be awarded for each of the 10 or so different projects)
Col I Is the number of payments available (this is worked out by the number of months between the start and planned end date inclusive, i.e. jan 09 to July 09 = 7 months, Is there a formula to work this out?)
Col J is Col H divided by Col I
Col K is Payments YTD
Col L is Earned Current Month and it's where I really need help, What I want to be able to do is work out how many payments are due by the % of work carried out. All people get one payment at the start of the projects. then one each month if they have reached the required %, the % target depends on the number of months between the start and end date, for instance 4 months will means 1 payment every 25% but payments cannot be given in advance, so if one person completes a 6 month project in two months the most they can have is two payments, and once 100% is reached the final payment can only be given once the completion date is entered in the % complete column.

I hope this makes sense and I hope somebody can help me on this.

If you want to see the spreadsheet to help make sense please PM me and I will arrange something.

Thanks in Advance

I have a pivot table that has "date" as a row field and "sum of charges" in the data field.
In a macro I need to remove "sum of charges" and insert "sum of payments". Both Charges and Payments columns are included in the PT source. How may I do this in vb?

(I have an area on my worksheet referencing the PT and is the source for my chart).

Thanks very much..

I have a excel file with 8000 rows. It is an worksheet of AR invoice aging.
I want to be able to create spreadsheets for the following. 1. Total Company
Aging - (summary of this spredsheet) 2. Summary Aging per Customer #
(summary of each customer) I have a total of 400 customers in this file. I
don't know how to use VGA - just formuals. I can go the vlook ups, but I am
just a beginner with those. Any suggestions/ideas as to how I can manage
this data? If you have other ideas I would like to hear them. I know
someone out there has done this before. At my old job I had some programers
that could do this for me. Here I am that person if I want the report.
HELP!! Thanks!
EX: these are the column headings that I have. I have to manually age
into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120 >120

Hello everyone-

I need to know how to sum the entries in one column that belong to duplicate data in another column. In my case, I have account numbers (many thousands of them) listed in one column with customer payments in another column. Account numbers may have only one payment for some customers or multiple payments on other customers (customers with multiple children have multiple payments but only one account number). I want to sum the payments of customers with multiple payments and have the sum total added to a new column in Excel. I am not great with Excel, so formulas would work better in my case than macros (never done one) or VBA (whatever that means) if possible. Many thanks in advance for your help.

I have a list of vendors, with corresponding data (account numbers and payments made) per vendor in a specific time frame.

For each vendor, I need to count how many times each account number appears. It's not the same as the number of payments, because each account no. can have multiple payments associated with it. So simply counting the account numbers for each vendor returns a much higher number.

In my attached sample, cell C31 is where I need the formula. In the data I show, there are 16 unique account numbers listed. So the formula should return a result of 16 in my sample.


Hello there

I'm looking for some help with the following scenario.

This spreadsheet has a date coloumn (i.e. A1:A200) and a payments coloumn (i.e. B1:B200), and the current date in C1.

I have been using the following formula to calculate payments received in the last month, but only on one worksheet.

What I need to do is extend this formula to cover payments recorded over several sheets (same coloumn layout on each sheet) within the same workbook and return the result in one cell on a summary sheet.



Thanks in advance, any help greatly appreciated.

I am preparing an analysis of my company's expenses for the last two years by account. One on excel sheet I have the raw data,

bank account, payee, payment date, clear date, amount, payment type

(This is from all accounts for the whole 2 year period.)

In the second sheet, I have a summary schedule setup. The summary is setup like this:

Account #
Row 1: Begin Date
Row 2: End Date

Payment Type 1 AAAAAAA
Payment Type 2 BBBBBBBB

I would like to come up with a formula that will populate AAAAA & BBBBB for a given account during a given time period. For example, I would like it to give me the total of Type 1 payments from account 12 during the period January 2, 2005 through January 27, 2006.

Right now it is only giving me the info for 1 payment type and 1 account. Here is what I have now {=SUM(IF(Detail!$G$10:$G$3942>=R$5,IF(Detail!$G$10:$G$3942


I know there are many questions on this topic already, but I have tried the different formulas, and they are not working for me.

Basically I just need to do a simple aging for health claims, using the Date of Service (DOS) and paid amounts.


Patient DOS Paid Amount

John Doe 20100801 15.55
Jane Doe 20100601 62.00

All i want is a formula or solution to go through about 10000 claims like above and just sort the data into Current (0-30 days), 31-60, 61-90, 91-180, 181-360, over 360

Also, Column G has the Date of Service, Column P has the Paid Amount
and Columns R, S, T, U, V, W are the aging columns respectively.


Hello all... I have attached a spreadsheet that's sort of like a finances timetable. It shows the ups and downs of a balance throughout the year (including future balances based on projected payments). What I want to do is have a display on the top row that shows what my minimum future balance will be and what date that will occur. I have a cell right now showing the minimum of the column, but I want the range to be only from today forward.

The logic (in my head) would be:

1) find the minimum in column D,

2) compare the date in column A of that same row to today's date and see if it is equal or greater to today,

3) if the date in column A is greater than or equal to today's date, then display the balance and the balance date on the top row,

4) if the date in column A is less than today's date, then find the next lowest minimum balance in column D, repeat the same date check, and continue until the "greater than or equal to today's date" argument is satisfied.

I have no idea if this is possible within the confines of Excel, but if anybody had any ideas or suggestions, they would be most appreciated! Thanks!


I use aging reports at work for accounts which I import into excel 2003 each month. These are worked according to amounts owed on the accounts and how far past due they are. A lot or most of these accounts are carried forward from last months report because they are still in aging again the next month. I would like to link the individual accounts either by workbook or worksheet when they are a repeat from the previous month or months report to avoid having to research the account anew.

This way I would know I worked on this account last month and would have access to the notes from the previous month and even though they are on a new report it would be like a continuation of work done on the account rather than having just this month's information

I am comfortable writing a sumif formula in excel, but am not as familiar with Access. I have a table that has a list of account numbers and a list of charges on the account. For instance, I have 10 records with the same account number, but different charges for each record. I need to figure out how to query the data so that I can produce a sum of charges for each account number. My excel formula would be, =sumif($C$2:$C$5000,C2,$W$2:$W$5000). I need to to translate that into the appropriate Access format. Any help would be much appreciated.


Need a formula to enter invoice amount in correct aging column by comparing invoice date to a random date entered in cell A1 to determine days outstanding. See attached worksheet.


got little bit troubles with my work, hope somebody will help:

On the first sheet (payments) I've got several payments we have received (column A are payment IDs) with any values (column B).
On the second sheet (sums) I've got several summary invoices (column A = sum ID) for any values (column B).
For each summary value I need to get all of the combinations of payments that will correspond with the summary value with tolerance of +- 1.00
Need to get back payment IDs and the sum of those payments.

I hope screenshots will help to imagine better. In the column C,D etc.. you can see possible right options.. the way i need to get back the result.

sheet (payments)


Here is the code, so you don't have to write the numbers manually:

payID	 payVAL 
1	 105,10 
2	 2 001,00 
3	 135,00 
4	 209,00 
5	 2 001,50 
6	 18 000,00 
7	 55,90 
8	 102,65 
9	 0,09 
10	 0,99 
11	 109,80 
12	 88,00 
13	 900,10 
14	 78,00 
15	 1 327,58 
16	 4 182,17 
17	 109,00 
18	 780,77


sumID	sumVAL
1	4504,6
2	4451,6

ofc i will have there more than 2 summary values.. but this is just a short example.. ;o

I was looking at Microsoft's website and they had a nice amortization schedule (see attached) but I have a few problems and questions with it.

1. If you have a base year of 2005 and a base month of 6, the Payments in the First 12 Months box shows payments for January through May. That isn't right. If it started in June, there wouldn't be any payments right?

2. It would be nice if it had a drop down for principal and interest loan versus a interest only loan. I know there's 0 amortization for interest only but it would be nice to have a spreadsheet that adapts for that as well. Any ideas?

3. How can I make this schedule account for a mortgage that begins in the middle of the month? I would only want to pay half that month's payment. Not so worried about days but would like it to be able to account for a mortgage that begins in the middle of the month.


I need to create a formula to look up 2 dates from another report and bring back a total $ for those within that date range.

Example is below...this is the summary sheet. On another report will have my data. If the amount billed was in Jan but received in march, it would go in the march column. Sort of like an AR aging.

Jan Feb Mar
Jan $50

Please help.

Thank you,

I want to calculate the number of payments that have been made on a mortgage between two dates (the first date being the first payment date and the other date being some random date in the future).

Payments are made monthly on the same day of the month. So, if the first payment date is 2/15/1999 then the next payment will be made on 3/15/1999, and the third payment on 4/15/1999, etc.

So basically I need a formula that counts how many times a particular day (determined by the day of first payment) occurs each month between two dates, e.g. between 5/15/2005 and 7/14//2006 the 15th day of the month occurs 14 times (including the first payment date).

Let's say I want to know how many payments were made between 2/1/1999 and 3/25/2011 (REMEMBER: payments are made on the 1st of the month in this case), what would the formula be for figuring that out???

A1: First Payment Date
A2: Second Date (any future date, not necessarily a payment date)

I've used, to no avail, =DATEIF(A1,A2,"m"). This function doesn't account for the fact that payments are made on the same day of each month. For example, with A1:1/1/2011 and A2: 3/31/2011 the formula returns 2 months even though 3 months of payments would have been made.

Can you produce a formula that counts the number of payments between two dates, recognizing that the day of first payment will be the day on which each following monthly payment is made???

THanks in advance.

Hi Guys,

I am new to this forum. I have been working on this for the last 3 years to no avail, I just dont understand how to obtain what I am after and could really use a hand!

I am working with a set of invoices issued to a "customer". Each invoice has a date, and must be paid within 14 days otherwise interest accrues at the rates provided for in my attached workbook.

Over the years the customer has incurred various bills as can be seen, and made various payments. There is an amount oustanding.

I want to Workout the interest owing total and I am wondering what the best way to do this would be? I have a list of bills and payments owing with payments made and the date of each.
- I know my interest rate
- I know my overdue dates as that would be date of payment minus date due.

Im getting stuck though in effecting this.

I think I need a running total of the "amount oustanding" and instead of applying each payment to a partciular invoice, it should be each payment goes toward the "amount oustanding" and interest is worked out date to date between each payment and charged accordingly.

I have a attachedan IMAGE of what I think it should look like, but just dont know how to put this into excel.


INTEREST CHARGES.xlsx Excel Schedule (Updated)

interest.jpg (sample calculation image)