Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Counting The Number Of Dates In An Aging Bucket

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

I am trying to report on the number of dates that fall between standard aging dates (30-60-90-120+) given the TODAY() function. See attached, i need to count how many clients have had a "Service Campaign" run in the last 30-60-90 days.

View Answers     

Similar Excel Tutorials

Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...
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 ...
Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
Get the Number of Workdays Between Two Dates in Excel
How to calculate the total number of working days between two dates in Excel.  This allows you to exclude all weeke ...

Helpful Excel Macros

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
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

Similar Topics







I need a formula that will help determine what "bucket" (based on today's date & due date) a date would fall under. I have 5 buckets:

RRC
0-14 Days
15-22 Days
23-30 Days
31+ Days

I am using the following formula:

=IF(D2="","N/A",IF(TODAY()-D2-26,TODAY()-D2-12,TODAY()-D2-5,TODAY()-D20,"31 + days"))))))

This formula works fine, however the next day I run the report the formula has to be slightly modified to place everything in the correct aging "bucket". Is there a formula that would alleviate the need to update the formula everytime I would like to check the inventory?

The dates are actually based on a weekly (Sunday through Saturday) schedule. So if the Due date is Prior to today's date it would fall into the 31+ category. If due up to 14 days (including today) it would fall under the 0-14 Day "bucket". And so on...

Any help is greatly appreciated.


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.


I am trying to create an issues log that includes the aging of issues that are in "open" or "in progress" status for the following periods of time:
0-30 days
31-60 days
61-90 days
91-120 days
over 120 days

The aging should be from the date the issue was opened to the current date (including weekends). Once the aging is calculated, i want to be able to count the number of issues that fall within each range.


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.


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




hi excel experts

i looking for some result from two column

like A and B have dates column named A as SUBMIT AND B as RECEIVED

1. If No Date In Sumit And Date Is In Received Means Result Blank
2. If Date In Submit And No Date In Received Means Result Aging With Today Date
3. If Date In Submit And Received Also Result Aging Between Submit And Received
4. If Both Column Blank Result Blank
here formula before logica test,

for 2 result CLEAN(IF(TODAY()-AA7>=30,">30 Days",IF(TODAY()-AA7>20,"21-30 Days",IF(TODAY()-AA7>15,"16-20 Days",IF(TODAY()-AA7>10,"11-15 Days",IF(TODAY()-AA7>5,"6-10 Days",IF(TODAY()-AA7>3,"3-5 Days","0-2 Days")))))))

for 3 result CLEAN(IF(AA7-AB7>=30,">30 Days",IF(AA7-AB7>20,"21-30 Days",IF(AA7-AB7>15,"16-20 Days",IF(AA7-AB7>10,"11-15 Days",IF(AA7-AB7>5,"6-10 Days",IF(AA7-AB7>3,"3-5 Days","0-2 Days"))))))))


find the attachment... you understand than me....



hi excel experts

i looking for some result from two column

like A and B have dates column named A as SUBMIT AND B as RECEIVED

1. If No Date In Sumit And Date Is In Received Means Result Blank
2. If Date In Submit And No Date In Received Means Result Aging With Today Date
3. If Date In Submit And Received Also Result Aging Between Submit And Received
4. If Both Column Blank Result Blank
here formula before logica test,

for 2 result CLEAN(IF(TODAY()-AA7>=30,">30 Days",IF(TODAY()-AA7>20,"21-30 Days",IF(TODAY()-AA7>15,"16-20 Days",IF(TODAY()-AA7>10,"11-15 Days",IF(TODAY()-AA7>5,"6-10 Days",IF(TODAY()-AA7>3,"3-5 Days","0-2 Days")))))))

for 3 result CLEAN(IF(AA7-AB7>=30,">30 Days",IF(AA7-AB7>20,"21-30 Days",IF(AA7-AB7>15,"16-20 Days",IF(AA7-AB7>10,"11-15 Days",IF(AA7-AB7>5,"6-10 Days",IF(AA7-AB7>3,"3-5 Days","0-2 Days"))))))))


find the attachment... you understand than me....

Hello
New to board...looking for some help for a report.

I have a workbook with several sheets in it and I need to create an aging report referening up to four of these worksheets.

To keep it simple..let's assume that in each worksheet A1:A100 is full of individual dates.

What I would let to know how to do is how to count up the total number of dates in A1:A100 for each sheet and categorize them as follows
1) Older than 6 months
2) Between 3-6 months
3) Between 1-3 months
4) Less than 1 month

I am not very good at this...and could only get a count of dates older than today
=COUNTIF('Open Problems'!A3:A28,"



dear expert

I HAVE DATA IN 4 COLUMN CONTINUSLY BUT I WANT MY CALCULATE SHOULD OCCUR ONLY WITH 3COLUMN DEPENDS.

I HAVE ARRANGE AGING FOR CALCULATEION WITH BUSINESS AND STATUS
I HAVE AGING IN TWO COLUMN COMMITTED AND NON COMMITED


ZSP1-YES- COMMITTED RESULT 1 OR 2 OR 3
ZLAB-YES-NON COMMITED RESULT 1 OR 2 OR 3
BRING-YES-NON COMMITTED RESULT 1 OR 2 OR 3


IF ZSP1 IN COLUMN B:B THEN YES IN COLUMN C:C THEN SHOULD CHECK AGING IN D:D COLUMN " COMMITTED " WITH THE AGING IF > 30 DAYS RESULT SHOULD BE 1, 21-30 DAYS RESULT SHOULD BE 1, ,IF 11-20 DAYS RESULT SHOULD BE 2,IF 6-10 DAYS RESULT SHOULD BE 2, IF 3-5 DAYS RESULT SHOULD BE 2, IF 0-2 DAYS RESULT SHOULD BE 3....

IF ZLAB IN COLUMN B:B THEN YES IN COLUMN C:C THEN SHOULD CHECK AGING IN E:E COLUMN " NON COMMITTED " WITH THE AGINGIF ZSP1 IN COLUMN B:B THEN YES IN COLUMN C:C THEN CHECK AGING IN D:D COLUMN " COMMITTED " WITH THE AGING IF > 30 DAYS RESULT SHOULD BE 1,OR ,IF 21-30 DAYS RESULT SHOULD BE 1,OR ,IF 11-20 DAYS RESULT SHOULD BE 2,OR,IF 6-10 DAYS RESULT SHOULD BE 2, OR ,IF 3-5 DAYS RESULT SHOULD BE 2,OR, IF 0-2 DAYS RESULT SHOULD BE 3........

IF BRING IN COLUMN B:B THEN YES IN COLUMN C THEN SHOULD CHECK AGING IN E:E COLUMN " NON COMMITTED " WITH THE AGING IF ZSP1 IN COLUMN B:B THEN YES IN COLUMN C:C THEN CHECK AGING IN D:D COLUMN " COMMITTED " WITH THE AGING IF > 30 DAYS RESULT SHOULD BE 1,OR ,IF 21-30 DAYS RESULT SHOULD BE 1,OR ,IF 11-20 DAYS RESULT SHOULD BE 2,OR,IF 6-10 DAYS RESULT SHOULD BE 2, OR ,IF 3-5 DAYS RESULT SHOULD BE 2,OR, IF 0-2 DAYS RESULT SHOULD BE 3....

CHECK THE ATTAHMENT IT WILL BE EASY UNDERSTANDING THAN MY BRIEF QUTATION

I am trying to create a database with due dates for work. For Example.

Due in 7 Days =COUNTIF(A7:A40,"<="&TODAY()+7)-COUNTIF(A7:A40,"<"&TODAY())
Due in 30 Days =COUNTIF(A7:A40,"<="&TODAY()+30)-COUNTIF(A7:A40,"<"&TODAY())
Due in 60 Days =COUNTIF(A7:A40,"<="&TODAY()+60)-COUNTIF(A7:A40,"<"&TODAY())
Due in 90 Days =COUNTIF(A7:A40,"<="&TODAY()+90)-COUNTIF(A7:A40,"<"&TODAY())
Lapsed<Today =COUNTIF(A7:A40,"<"&TODAY())

I think these work for me in excel, but I'm not a master at this and for some reason when I read my functions I swear they are contradicting themselves. The reason I feel the functions contradict themselves is because I can remove the -countif and it still gets the same answer.

For the date ranges 90, 60, 30 & 7 I do not want dates counted that are less than "today" or ex. = a negative number. They would be considered "Lapsed"

Please see the attached file. Any help would be greatly appreciated!!!!!

Kevin


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_


Hi,

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
Feb
Mar

Please help.

Thank you,


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 am trying to get an aging from information. I need a formula that allows me to age data in a range over the last 7 days, 8 to 14 days, 15 to 21 days, 22 to 28 days and greater than 28 days. The problem is this aging has to adjust automatically on a daily basis.

Here's an example of the data:

INV # INV. DATE AMOUNT
529721 3/1/2007 268,135.00
529828 3/2/2007 239,004.00
529829 3/5/2007 312,310.00
529841 3/21/2007 613,108.00
529845 3/29/2007 160,720.00
529846 4/1/2007 603,920.00
529847 4/13/2007 15,420.00
529848 4/19/2007 9,675.00

Assuming the date is 4/20/07, I want to get a report that shows:

Last 7 days (4/20 - 4/13) 25,095
8 to 14 days (4/12 - 4/5) 0
15 to 21 days (4/4 - 3/28) 764,640
22 to 28 days (3/27 - 3/20) 613,108
Greater than 28 819,449


I've been trying to use Sumif, but that doesn't seem to work since I am looking at a date range instead of a set number.

Any suggestions?


Hi, I can't figure this out.

I need to count the number of days between two days. I can't use something like NETWORKDAYS though, because the days I need to count I have defines in other formula.

So what I need to do is this (not sure I can actually post a spreadsheet, but I could try if I need to):

I have two days, lets say in A1 the beginning date, and in B1 the ending date.

Then in a column, Lets say column C (Lets say C1:C100) I have a list of dates I have defined.

How do I count the number of dates in column C that fall between the two dates at A1 and B1 -I want the results to also include the dates at A1 and B1. So, it the dates at A1 and B1 were 1/1/08 and 1/5/08, and the list at column C included, 1/1/08, 1/2/08, and 1/5/08, the formula would return a count of three (3).

I hope that is clear enough.

Thanks


Hello Excel experts,
I got this problem I can’t solve, maybe it is easy to solve, but I am fairly new to writing functions in excel.
I got a lot of different dates in single column, what I need is to pull the dates if they fall in to the range, from today till 30 days from now.
I understand it calls for array formula.
Please look at attached sample workbook “dates”.
Any good suggestions,
I thank you in advance!


Hello Excel experts,
I got this problem I can’t solve, maybe it is easy to solve, but I am fairly new to writing functions in excel.
I got a lot of different dates in single column, what I need is to pull the dates if they fall in to the range, from today till 30 days from now.
I understand it calls for array formula.
Please look at attached sample workbook “dates”.
Any good suggestions,
I thank you in advance!


First, I want to say I love this board. Its amazing how many of my questions have already been answered.

I am using Excel 2000 and have been asked to create an aging chart. I'd like to say that Charts are not my strong suit (although when I'm done with my current project I'll be an expert )

I have a column chart of the number of new trouble tickets incoming for each week.

I have # of units on the Y axis and dates (as text) on the X axis to represent the week ending.

Now I've been asked to "turn the chart on its right side and create an aging chart with 30, 60, 90 days along the bottom"

So now I am going to chart the number of days the trouble ticket is open.

Not only can I not get the 30, 60 ,90 along the bottom but I'm having a hard time figuring out what to chart along the other axis. The person requesting this chart is unavailable. I keep picking bar chart but suspect there is a better choice.

I have searched the web looking for examples of charts and something showing the number of days along the bottom. I keep feeling there's some slick combination that will give me what I want.


Anyone out there have some ideas of the best way of showing this data and then how to do the chart?

Thank you.

Kathy Zawacki


Hello, any help would be greatly appreciated. I'm new to excel and so I apologize if I'm not using the correct technical terms.

I work in AR and I get a weekly aging report in excel featuring all of the customers I handle (about 400-500). Here are the standard columns that come with the aging

Customer# Cust. Name Total Balance Amount Past Due



I added some columns to help me keep up with each customers info. These columns are

Comments Oldest Invoice Check Back Date Email (Y or N)



So I filled in the info in the 2nd set of columns for this weeks aging. When next weeks aging comes in, it will just have the standard 1st set of columns. Any customer who paid off their balance will not be on the new aging, and any new customers will be added to the new aging.

Is there some kind of formula or macro I can use to take ONLY the info from the 2nd set of columns (Comments, Oldest Invoice, Check Back Date, Email) and add it to the same customer in the new aging?


HI
Any one can teach me how to calculate aging, from the date created to current date.

Aging Type:
Less Than 7 days
Less Than 15 days
Less Than 30 days
More than 30 days

Thank You
Regards
Rashim


Hello,

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.

Example:

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.

THANK YOU IN ADVANCE!


After looking thru the forum for some time, I have been unable to find a solution that could be adapted to my problem. I am trying to create a past due (aging) report. The data would be pulled from approx 400 worksheets (one for each contract number). Each worksheet is identical, as far as the information locations. The worksheet contains this information:

B1 = Contract Number

Bill Date(colA) Princ(colB) Int(colC) Total (colD) Running Balance (colE)
1/7/09_______$49.21___ $46.08__ $95.29 _____$95.29
2/3/09______ $49.64___ $45.65__ $95.29_____ $190.58
01/00/00____ $ 0.00____ $ 0.00__ $ 0.00_____ $190.58


This billing information is pulled in from another data input worksheet.
The running balance will reflect any payments that have posted to the account. The sheet is set up with 12 rows of data (one for each month), so there are "01/00/00" dates that have not been filled in yet.

I am hoping to be able to do this without having to change anything in these worksheets, as there are so many involved. (Having all formulas needed built into the new worksheet.)

I would like the Past Due (aging) report to be in a new sheet and look something similar to this:

Contract# Current 15-45 days 45-89 days over 90 days
486925__ $0.00__ $95.29___ $95.29____ $0.00


Am I asking the impossible? I only know the basics of Excel and then just enough to be dangerous.

Any advice/help will be greatly appreciated.
JW


I have a table with CustomerID field and a CurrentDate field



I basically want to create an aging - counting the IDs where the current date field falls within a particular aging, in the end I don't even care what the customer IDs are - just want to count them

I'm using QBE

The first column would be the customer ID field

The next would be the actual functioning version of this:

expr: iif([currentdate](between (DateAdd("m",-31,#10/5/2008#) And (DateAdd("m",-36,#10/5/2008#)),1,0)


I would have a number of these columns like the above 31-36mos, 25-30 mos, 19-24mos to count records in each date range


The dateadd calcs work individually, but I don't know if I can use the between/and with iif or if I have to use > < instead, or if I can do it that way at all, I've tried a couple of different ways, but I can't quite get it...


Maybe there's a completely better way to do this? Not looking for vb code unless it's the only way!


Any help would be gratefully appreciated.

TIA!


Hello to all of you,

I have the following simple sheet:

http://www.pichotel.com/pic/22437iBRhH/163774.jpg

I have an age in days in the column F. I try to find the righ if formula to calculate in which treshold the case falls into.

I'd like the formula to return 30-60 if the aging is between 30 and 60. Display 60-90 if the value is between 61 and 90; 90-120 is the aging is between 91 and 120 and Above 120 if the aging is greater than 121 days.

I tried to nest the Ifs without success so far

Thanks a lot for your help!!!!!


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:
Code:

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:
Code:

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!

Code:

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:
Code:

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:
Code:

Current	31-  60	61-  90	91-  120	121 & Over	Future
116.04 (128.80)	0.00 	0.00 	0.00 	0.00