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 ...
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 ...
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 ...
Vlookup on Dates and Times in Excel
Perform a lookup on dates and times in Excel: vlookup, hlookup, index/match, any kind of lookup. This tutorial show ...
Sorting Data by Date, Text, or Number in Excel
Sorting Data can be done with a few quick clicks of the mouse. I have used the same data as the previous 2 tutorial ...

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


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


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


Hi,

I wan to calculate the aging between two date and time stamps, the result should be in no. of days. But it should exclude the weekends and the holiday list as well.

Date 1 7/23/2009 10:00 Date 2 Today's date Holiday List 1-Jan-10 26-Jan-10 27-Jan-10 10-Apr-09 1-May-10 9-Aug-09 20-Sep-09 27-Nov-09 25-Dec-09


Hi every one... Can you help me in making formula of my Aging Report. I attached already the file for editing. Any kind of effort is very much appreciated. Thank you so much. More power to this site..


Hi All,
Please provide the formula for below aging.
0 < 90 days
90 >90-180 Days
180 >180 Days
Column M has the aging details.
I am attaching the file below.
Thank you in advance for the help.


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

Hi Everybody!

Is there anyone have the formula of creating Aging Reports in Excel.


This one is going to be pretty easy - at least for you all

What i have is a list of dates in a row. I want to set up a countif formula to count the number of date that fall with in 90 days of todays date.

I'm planning on placing todays date in a cell in the upper left hand corner (cell A2) above the list of dates. This way i can just type in todays date and the spreedsheet will count the number of dates, in the list, that fall with in 90 days of the date i type into cell A2

Thanks in advance.


Worksheet is an action log. It has a column containing "due dates".

I want to count all rows (dates) that are "less than" today (to see what work is overdue).

I also want to count all rows (dates) that will come due in a week.

Conditional formatting highlights them OK but I also want to maintain a set of counts.

I can do all this in a macro but I don't want to use macros, just formulae.

Any ideas?
Thanks in advance.


I need to be able to determine an employee's seniority date (based on actual time worked) between several dates.
The example is that the original hire date is:
hire(rehire) dates term dates
3/31/2000 2/6/2004
1/5/2005 10/5/2007
9/22/2008

The length of service the amount of time served between the hire(re) dates and the term dates and also between the last hire and today.
The seniority date would have to be moved from 3/31/00 to another date that incorporates the length of his absences (something around 22 months and 17 days later).

Thank you!


I have a column of dates. I want to count the number of dates that are six
months or less from today. Any help would be appreciated.
kaye



I want to calculate the number of days between 2 dates but want to exclude dates which fall on Saturdays or Sunday in any calendar year. Does anyone know a formula to do this.


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.


I have a spreadsheet that has dates copied into it, these dates are then looked up from another sheet to get a figure for how many dates in that month are in a column, such as:

Below is Sheet1 Column A

30/10/2007
30/11/2007
30/11/2007
30/11/2007
30/12/2007
30/12/2007

So Sheet2 uses the COUNTIF function to count any november dates but it does it like this =COUNTIF(Sheet1!A:A,"**/11/2007") but because the dates have been copied in from other spreadsheets sometimes it is set to text format and others to date format but when i try to alter the whole column to text format so the countif function picks all the dates up it obviously turns the dates in 5 number figures which the function doesnt pick up. can someone either tell a different function that will pick it up even if it is in text or date format a way to quickly edit the cells to text format without chnanging the layout of the dates.


HI...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)




Question: In Microsoft Excel 2010, is there a way to automatically highlight past due dates bases on a range of days?

For example, I have a column with a lists of dates depicting when milestones are due. I would like Excel to highlight dates using conditional formatting based on how long they are overdue. I would like to highlight milestones over 60 to 90 days overdue in yellow, Actions 90 to 120 days overdue in orange and Actions over 120 days overdue in red. Based on todays date.

I have created the following formula for the Actions over 120 days overdue in red:

=AND(F1<>"",F1<TODAY()-121)

It works fine.

I just cant get the range to work properly. i.e. milestones over 60 to 90 days overdue or Actions 90 to 120 days overdue . This is the formula that I was working on and cant get to work.

=AND(F1-TODAY()>=-60,F1-TODAY()<=-90)
=AND(F1<>"",F1<TODAY()<=-120,F1<>"",F1<TODAY()>=-90)

Any assistance is greatly appreciated.

Hey Guys

I have one that stumped me! I am using 2003 excel and I need to create a summary on aging stock. The data that I get is in CSV format imported as text into excel. It has the dates like this: yyyy-mm-dd. I tried to group the dates to be monthly. But excel PIV returns this message: Can not group selection. So I did some research and noticed blanks. So I created a dummy date to replace the blanks, eg: 1950-01-01. eg:=IF(M2=" ","1950-01-01",M2).

Now it still will not let me group the dates? So I checked the formats and it is all in TXT format (check). I tried to change the date to the numeric version "DATEVALUE(W2)" and the PIV will still not group it? I can't find a way to have it group the dates that I have on my data. Is there something that I am missing here?

Any help will be much appreciated!!!


Good day,

I want to calculate the number of Saturday & Sunday's between two dates. For example in cell A1 I have 27-Feb-07 and in B1 I have 20-Mar-07 ; is there a formula to calculate the difference the number of Weekend days between those two dates. So in this example it would be 6 days as the answer. The weekends I am refering to are Saturday and Sunday.

Thank you for your help,


Hi,

I have a database where in dates are repeated in column D and names in
column I.
I want a formula in a different worksheet of the same file wherein I get the
count of dates column along with the individual name.
The intention is to get number of days each individual is present. The same
dates could be repeated any number of times for the same individual.

Example for the month of Dec. 2005
Dates Names
20 AD
15 BC
08 GH........etc

Cheers,
Mandeep Dhami