Free Microsoft Excel 2013 Quick Reference

Aging Report

I ve worksheet with
Supplier Name
Inv No.
Inv Date
Amount
I wmant to make an aging report, could it possible with pivot table


Post your answer or comment

comments powered by Disqus
Hello there

I am sure this is a pretty simple problem for all the excel experts out there...unfortunately I am not one of them

I am trying to generate an aging report (ie Current, >30, >60, >90) by referencing a worksheet that has A1:A100 populated with dates.

What I would like to do is count the number of dates in column A and reference current date populate a separate worksheet with a count total of how many fall into current, >30, >60 days etc etc

Not sure I explained it properly but hopefully someone can help.

Hi,
I'm trying to create an Accounts Receivable "aging report", using Excel
2003. I've got a field (a date field), that provides the date of invoice.
I'd like then to add 30, 60, or 90 days from that date, and call that the
"Due Date" for payment in another column.

Thanks,
Peter

I receive an AR report showing statements billed and payments made by
customers for the last 12 months. It also shows the current balance.
I have been asked to create an aging report that determines if any of
the current balance is 30, 60 or 90 days overdue. Statements and
Payments are on separate rows and can vary widely. For example one
report I saw showed 5 months unpaid and when the customer finally did
pay they only part of one bill. Accounting always pays the older bill
first. I understand how to create the days overdue formulas but to
track unpaid amounts from over many rows is causing me a headache. I
can also write a VBA program to “look-back” several rows to determine
which bill this partial payment should be applied to but that started
to get way too complex. There must be a simpler way to determine
aging on the current balance. Any help will be appreciated.

Good Afternoon,

I currently have an aging report with the below data:

1-30 Days 31-60 Days 61 - 90 Days Sum
a. -2491.02 100 368.98 -2022.04
b. -5,000 29443.04 -5,000 19,443.04
c. 10.18 5.00 28.95 44.13
d.

What I require is that for the greatest absolute value to be populated in
the below table which will show which period the overall sum of aging
pertains to. I have used IF statements however I was unable to get this to
work due to the negative amounts.

1-30 Days 31-60 Days 61 - 90 Days
a. -2022.04
b. 19,443.04
c. 44.13

Please let me know if you have a solution for this.

Leo

I save an SAP aging report as an Excel spreadsheet, but the formatting needs
to be done. I would like to create a macro or a template so that the report
is formatted to my specific needs.

I am trying to use the function SumIf to come up with totals for 30, 60, 90
day sub-totals for a list of invoices. Column C has the invoice dates and
column Q has the outstanding balance due on the invoice. I have found that I
could not get any totals when using Today() function in the criteria, I could
only use the exact date for either the 30 day or the 90 day situation. I
could not figure out a way to find subtotals for dates between 60 and 90. For
the 30 day I am using a criteria for anything less than 30 days and then
subtracting the totals for the other two sub-totals. The forumlas I have used
as listed below:
Thirty Day: =SUMIF($C$7:$C$200,"

hi i am looking for Project Aging Report, if you all savvy people can help me with some sample , would be great

Thanks in advance

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

Hello,

I am trying to customize an aging report, column will be 30, 60, 90 120 + I would like the amount to show under each column. The charge amount for $252.38 each day. Thanks

Hi,
I'm trying to create an Accounts Receivable "aging report", using Excel
2003. I've got a field (a date field), that provides the date of invoice.
I'd like then to add 30, 60, or 90 days from that date, and call that the
"Due Date" for payment in another column.

Thanks,
Peter

Dear all, pl. help me this.

I have three columns

Item No. > Date of issue > Qty

Now i want ageing report like;
Qty issued on every three months gap.

e.g. qty issued
Item No. 3months 6 months 9months

regards.chirag

Hello,

I need your help in creating a Macro for preparing a Aging Report.

In the sample sheet I have Raw Data sheet and in that it has to look for Close time column and if it is empty it need to copy the data in another sheet named Aging Data (As in sample sheet).

I need the Pivot table as shown in the sample data and in that I have added Column H & I using today () function & below code manually. Please help me to automate it.

=IF(H4<=3,"0 to 3 days",(IF(AND(H4>=4,H4<=7)," 4 to 7 Days",(IF(AND(H4>=8,H4<=15)," 8
to 15 Days",(IF(H4>15,"> 15 Days","")))))))
Note: The data is not constant and vary.

Thanks in advance for your help!!

Regards,
Humac

Hi,
I need to develop a report showing aging of open issues going back several months (if not years). I can easily pull data using COUNTIFS and my data set for the current open issues in each of 5 buckets (120). However, I need to be able to see what should have been in those buckets for days, weeks, months past.

I have uploaded an example file here, and what I need to do is first find all issues of a certain type (type A and B), then count all issues that would have been in each bucket during the day listed in Column A of 'Aging'.

The idea I started to go with was this (example of C3 on Aging):
Code:
 
=COUNTIFS(Open_Date,CONCATENATE(C1,A3),Modified_Closed_Date,CONCATENATE(D1,A3),A3-Open_Date,CONCATENATE(D1,E1),A3-Open_Date,CONCATENATE(A1,F1))
But obviously the A3-Open_Date function doesn't work in COUNTIFS the way I want it to. I would love for it to subtract the Open_Date from the sample date for any item that meets the previous criteria, and then determine if that value is between 30 and 60 days, and then count all of those instances.

If anyone has any ideas on how I can do this, it would be greatly appreciated. Thanks for any help you can provide!

Indy_

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

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.
Daniel

Hello All,

This is my first post and I'm sure not the last.

I need HELP!

I have to construct an aging report weekly that will track the time duration between two dates and times during work hours ONLY, excluding weekends and holidays.

I can not find a formula that will give me what I need to complete this task.

Below is the format of the start and end dates for the tickets:

Start Date
6/10/2011 1:19:35 PM

End Date
6/10/2011 1:10:13 PM

Between these two date I have to find out how long it took for the ticket to close during business hours and not including weekends.

Work Week - Monday - Friday
Work Hours - 8AM - 6PM
Holiday - depended upon what holidays are in the month

The result should be in the days, hours format.aging_report.xls

Ive attached a example with the format and information.aging_report.xls

PLEASE HELP.

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

--
KimberlyL
------------------------------------------------------------------------
KimberlyL's Profile: http://www.excelforum.com/member.php...o&userid=23682
View this thread: http://www.excelforum.com/showthread...hreadid=373548

We have purchased another company and their programming for their AR Aging
report does not accurately apply credits against debits. The report looks
like there old invoices due but usually net to zero. Is there a quick
function or calculation in Excel that can help with the proper bucketing of
these amounts??

We can extract numbers on an "aging data" tab, but a subsequent pivot
table has the days listed in columns in either one of the following
order:

Over 1 year 90 60 365 30 270 180

or

0-30 181-270 271-365 31-60 61-90 Over 1 year

Obviously, we'd prefer that they be in a more usable sequence of 0-30,
31-60, etc. . .

Field settings/advanced allows a possible sort (ascending, or
descending), but still will not sort properly.

Perhaps the format of the source data?
Ideas are appreciated.

Pierre

I need to extract aging report in one column rather than various columns.
Any suggestion as to how I can automate the extraction of existing aging
report?

Eg : Existing aging report (various columns)

column A - Debtors Name
column B - Debtors Number
column C - 30 days
column D - 60 days
column E - 90 days
column F - 120 days
column G - more than 120 days
column H - Total of columns C1 to G1 (Debtors Balance)

Column C to G refer to various debtor's aging period

Require format :

column A - Debtors Name
column B - Debtors Number
column C - Debtors Aging
column H - Debtors Balance

I am looking for an EXCEL template or formulas to calculate inventory shelf
life by days. Something like an accounts receivable aging report. Is this
possible? If so, how?

I am looking for a formula in a aging report that will calculate in cell (E3). It needs to reference rows 6 thru 100 and the invoice dates are in column C and the balances are in column S.
As a invoice date (ex: C6) goes past 30 days i would like it to send the balance in S6 to cell E3, then when i drop a row and the date in C7 goes past 30 days the balance in S7 adds to E3 as well and so on right on through row 100.

I am using the formula =IF($Q$1>=($C$6+31),IF($Q$1<=($C$6+60),$S$6,""),"") this works great until i go to row 7 and then i cant get it to add the balances. I have trie :$C$100 after the $C$6 in the formula and it keeps giving a error.

Any help is greatly appreciated!!!

About a year ago I had someone help me with a re-aging function. It essentially took a bad aging report and fixed it, via a function.

For example, if I had these values:
Current	31-  60	61-  90	91-  120	121 & Over Future
(373.00)	0.00 	0.00 	472.00 	0.00   0.00
It would take the current and net it against the 91-120 to have only 99.00 in the 91-120. The code includes stuff for some odd situations, and apparantly there is a new one this year that throws off the calculation.

If I have these values:
Current	31-  60	61-  90	91-  120	121 & Over	Future
(128.80)	116.04 	0.00 	0.00 	0.00 	0.00
The function is returning -128.80 in Current instead of -12.76 in current.

I believe it has something to do with the total amount due being less than zero and it throws off the calculation.

The way I use the above function is actually like an array...I would enter this formula into 6 cells and confirm with CTRL-SHIFT-ENTER:
=IF($S2=1,aging($I2:$N2),"")

Can someone review the code and possibly identify where it needs to be tweaked? I greatly appreciate it (the code grew beyond my comprehension last year, so I have difficulty figuring out exactly what it is doing).

Thanks!

Function Aging(rg As Range)
Dim temp() As Variant
ReDim temp(1 To rg.Columns.Count)
  For i = 1 To rg.Columns.Count
      temp(i) = rg(i)
  Next
  themin = Application.Min(rg)
  If themin >= 0 Then
      Aging = rg
      Exit Function
  End If
      'assumes 1 negative
      'if there are >1 positive with future=0 then apply current right-to-left
      If temp(1) = 0 And temp(2) < 0 And Application.CountIf(rg, ">0") > 1 Then
Again:
        For i = 6 To 3 Step -1
            If temp(i) > 0 Then
                If Abs(temp(2)) < temp(i) Then
                    temp(i) = temp(i) + temp(2)
                    temp(2) = 0
                    GoTo 1
                Else
                    temp(2) = temp(2) + temp(i)
                    temp(i) = 0
                    GoTo Again
                End If
            End If
        Next
      End If
      leftover = Application.Sum(temp)
      If leftover > 0 Then
          If temp(1) > 0 Then
              For i = 2 To rg.Columns.Count
                  temp(i) = 0
              Next
              temp(1) = leftover
          Else
              For i = 2 To rg.Columns.Count
                  If temp(i) > 0 Then temp(i) = leftover Else temp(i) = 0
              Next
          End If
      Else
          For i = 2 To rg.Columns.Count
              If temp(i) < 0 Then temp(i) = leftover Else temp(i) = 0
          Next
      End If
      For i = 2 To 5
           If temp(i) <> 0 Then
           For j = i + 1 To 6
               temp(j) = 0
           Next
           End If
      Next
      n = 0
      For i = 1 To 6
           If temp(i) <> 0 Then n = n + 1
       Next
       If n > 1 Then temp(1) = 0
1:
  Aging = temp
End Function
One interesting note:

Although this doesn't work right:
Current	31-  60	61-  90	91-  120	121 & Over	Future
(128.80)	116.04 	0.00 	0.00 	0.00 	0.00
If you change it around a bit it will work right:
Current	31-  60	61-  90	91-  120	121 & Over	Future
116.04 (128.80)	0.00 	0.00 	0.00 	0.00


Hi,

I need to find a way to historically track aging of open issues (30, 60, 90, 120 days old). I have thousands of rows of data that include the open date, closed date, and a days old column (calculated as of TODAY for all currently open issues). I need to be able to fill a table with how many issues were open over 30, 60, 90, and 120 days for every week ending date since the beginning of the year.

Assume I have column A as the identifier, B as the Open Date, C as the Closed Date, and D as the Age.

I have a system that works for calculating this real-time (how many for each bucket as of right now), but I really need to be able to fill in each bucket for previous dates. I know nothing about VBA, so if there is a way to do this without VBA, that would be very helpful.

Thanks in advance for any help.


No luck finding an answer? You could always try Google.