Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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.


Similar Excel Video Tutorials

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
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f
WEEKNUM UDF for Excel Versions That Don't Contain This Function - UDF
- This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular

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

Hi all,

Just joined mrexcel.
I work at an advertising agency and I'm trying to do a report on how far into a campaign I am as a percentage.
i.e.
Start date Days into campaign Percentage
24/09/2010 ??? X%

Basically, I want days into campaign to be able to be changed as different lines will have different start dates and working them all out individually is time-consuming if I have 30 different lines on a campaign.

I tried the "TODAY" function but it wouldn't work if I did "TODAY/B12".

Is there any function that will be able to do this or will I have to manually calculate it all for the rest of my life?

Thanks


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

Could use some help.

I have a spreadsheet with multiple action items with due dates and owners. The due dates are in the J column and the owners in the I column.

I have created a countif statement to create an aging report. So for example, I count J column and count all records older than 90 days.... using the below formula

=COUNTIF(Open!$J$2:$J$387,"

I apologize for not making myself clear. I appreciated the response. And
allow me to qualify my original question further. I know I can do a pivot
table and obtain the results, but I am trying to avoid that.

See my example below - the question is how do I do a sumif function within
a summary view when I want to recap hundreds of rows by alpha FERC Codes and
sum associated values within the aging bucket column? I included an excerpt
of 5 records under 4 column headings, and then I show the summary of those
five records below by FERC Code by aging bucket. The countif function worked
when it came to counting the FERC codes, but I am stuck on calculating the
summary values associated to the FERC Code by aging bucket with the sumif
function. This summary section is giving me heartache. Thanks

Detail- Below is an excerpt. But we have hundreds of rows...
FERC Code FERC Current FERC >30 Days FERC > 60
TG 10
PG 20
PG 100
SG 300
TG 25
Total 5 10 420 25

This is what I am looking for below as the answer. Can one use the sumif
function to add values by within buckets by a FERC Code?

Count Current FERC >30 FERC >60
TG 2 10 0 25
PG 2 0 120 0
SG 1 0 300 0
Summary 5 10 420 25




Hi, first post. I'm working on a spreadsheet for my wife's private practice and need to count the number of days in a string like "3, 10, 17 Feb", which would be the dates of service in Feb. I'd need to come up with the value 3 in this case. I'd also need to come up with zero if no dates of service occured. The values would most likely be in the range of 0 to 5 as it's usually one day a week.

I can format or enter the dates in a different way if it helps. At the same time I'd like to keep it to one cell for one month, hence "3, 10, 17 Feb".

Thanks in advance,
Monte




Hello, I'm looking for a formula that will give me the most current date to the date on B1 out of only the "Active Clients" on my list. Its supposed to go in my report on L3 and then I need to count how many of those dates there are (which is supposed to go on M3). I tried using the Max function that will count all dates for the whole column and I only need the dates out of Active Clients.

I tried: =max(L4:L40)

See attached. Book4.xlsx

Thank you so much in advance

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,


All,
Ive been trying this for a while and seem to be getting no-where. In column A i have a list of dates (in dd/mm/yy format). I need to be able to pull off the following information at any given time:

Number of dates between:
27/06/2004 and 05/07/2006* and the earliest date that falls between these dates
06/07/2006 and 27/09/2006* and the earliest date that falls between these dates
28/09.2006 and TODAY and the earliest date that falls between these dates

Idealy i would like to only have to amend the 2 dates with the (*) as the other dates will auto update.

Does anybody have any ideas how to count the dates that fall between the 2 dates and how to work out the earliest one?

Any help is welcome.

Thanks.

Emma x


I need to count the number of dates in a single column that fall within a range. That range is defined as:

Today + 30
Today + 60
Today + 90

Thus, every day the result set would be different. The result I would want would be a number (that is, there are 4 rows that have dates that fall within the range of today + 30)

I've tried several variations on Countif, but not getting valid results. My dataset is on a different tab (if that makes any difference)

any help is appreciated!
Mike


i have two dates ... my input fields. im trying to create a function that counts the number of leap February 29ths that fall between the two dates, and returns that number (sumif, countif, sumproduct .. have tried all three a number of different ways). my imput dates will be no less than 1-Feb-88 and no greater than 1-Mar-2040. So basically, im working with a range of 14 dates, (29-Feb-88, 29-Feb-92 all the way to 29-Feb-40).

If the start date is 1-Jan-2004 and the end date is 1-May-16, the function will return "4", as 4 of the 14 dates fall in that range.

Any good ideas?!




Hi,

I have a workbook that logs client appointments, the bookings sheet lists the appointments for each selected month using the monday dates as the standard induction days, however clients can be inducted on any weekday so I have a seperate section that list all other client inductions that fall outside of the monday dates, the Monday dates work fine, i just cannot get the dates that fall outside of those dates to work.

Please see the attached sheet for further details.

Thanks in advance

JD



Good afternoon,

I have a spreadsheet that has dates enterered into cells in a column. I need a formula that will total the number of cells that have dates that were entered "today." I also need a formula that will reflect dates entered during the current monday-Friday week. These dates are old dates so counting when the date falls doesn't work.

thanks!

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