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


Advertisements


Free Excel Forum

Aging Formula

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

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


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
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the

Similar Topics







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?


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.


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


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 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...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)


I am having issue with subtotaling few calculated fields in pivot table. We generate report from ERP system that provides following items in Excel:
Customer Name Invoice Number Invoice Date(when the invoice was created) Sales Order Number Aging Date ( date when the report is exported to Excel) Invoice Amount (fnamount) Days Late ( Aging date invoice date) My report attempts to find invoices amounts that are due between 0 days to 30 days past, 31 days to 60 days, 61 days to 90 days and 90 days and above. I use if formula to properly categorize my due payments into correct column.

Here is the example of my If formula:
0 to 30 = IF('Days Late'30,'Days Late'

Hi, i am trying to make calculation (Total monthly basis) of aging base on below information, i have manage to do calculation for Pending case, but i unable to calculate close case

Pending Close Case
Less Than 7 days 0 Less Than 7 days 0
Less Than 15 days 4 Less Than 15 days 0
Less Than 30 days 18 Less Than 30 days 0
More than 30 days 15 More than 30 days 0
Attached is sample file

Thank You
Regards
Ra$$him


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.


I have an accounts receivable report and would like to insert a column that would show the age of the items in 30 day increments, up to 180 days. The date of the receivable is in column G and the date I am comparing it to is in cell R1. I would like to show current if it is up to 30 days, then 31-60 would show as 60 days, 61-90 as 90 days, 91-120 as 120 days, 121-150 as 150 days, and 151 days + as 190 days.

Thanks for your help.


Hi everyone,

I have a question about an AR aging spreadsheet. The aging table I download from our system does not show updated balances in each of 30, 60....days when there is a credit or return from a customer. Please click on the following link to see a sample of what I have and what I want to have in the table.

http://asalim.net/excel/AR_Aging.xls

Thanks for your help.

AHS.


i would like to do an aging in one column. These are my current columns. I would like one column to say Over 120,90, 60 or 30 continuing down the column when ever appropriate.

Here are my current columns,
Last Payment Date Current 30 days 60 days 90 days 2/9/2011 5266.95 3/23/2011 23284.69 2/23/2011 -1408.65 1/26/2011 -18371.56 2/9/2011 -8682.85
I am using the Last Payment date against the date of 04/03/2011.

These are the current formulas in the spreadsheet
Current=IF($C$1-AE3=31,$C$1-AE3=61,$C$1-AE390,U3,"")


Thanks


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.


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!


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 an Excel spreadsheet with the following:

Column G: Submit Date Aging (how many days old an item is)
Column K: Assigned Group (roughly about 25 different groups)

I have a total of 4 colums that I need help with:

Column T: 1-4 Days Old
Column U: 5-13 Days Old
Column V: 14-29 Days Old
Column W: 30+ Days Old

Need to seach column K for a particular Assigned Group, search column G for the Submit Date Aging against column K then add the numbers to the cells in column T, U, V and W.

For example, row 2, column T (cell T2) will be the total number of items that are 1-4 Days Old for one particular Assigned Group. Row 3, column T (cell T3) will be the total number of items that are 1-4 Days Old for a different particular Assigned Group. And so forth.

Thank you,
Keith


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.


Hi newbie here,
I don't have any macro knowledge but I am trying to create a macro that filters transaction records based on their aging days.
I need to filter transaction older than 2 months, 3 months, and so on. I don't know if it is possible to put the aging calculation on macro but I tried to calculate by excel worksheet formula and copy its value to my macro, no success.
Would somebody please give some knowledge. Been working on this for 4 days.


I have a list of orders with product info, sales clerk and sales info in a
spreadsheet. I am aging these orders so that I know if they are over 30
days, 60 days, 90 days etc... What I would like to do is copy all info in a
row to a seperate sheet if the number in the aging column is less than 30,
greater than 30, greater than 60 or greater than 90. How do I do this?



I am trying to nest several (7), IF/AND statements into a cell. My goal is to use my clients last payment date (A2) to produce an aging accounts dues column (D2).

IF A2 is <= 275, "Current"
IF A2 is > 305, But <=335, "Due in 60 Days"
IF A2 is > 335, But <=365, "Due in 30 Days"
IF A2 is > 395, But <=425, "30 days Past Due"
IF A2 is > 425, But <=455, "60 days Past Due"
IF A2 is > 455, But <=485, "90 days Past Due"
IF A2 is > 515, "120+ days Past Due"

While this feels like a simple IF AND combination, I am have difficulty getting it to work correctly.

Thanks.
Eric


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


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.


In sheet1 of d3 im using = IFERROR(OFFSET(INDEX(Sheet2!$B$1:$B$44,MATCH(Sheet1!$A$1,Sheet2!A1:A44)), ROWS(A$1:A1)-1,COLUMNS($A1:A1)-1),"")

I dont know it seems to work but when i use larger range then it does not give right figure...Could someone please validate this formula of mine?

If sheet2 col a matches sheet1 a1 value then i am wanting tis formula to show the result .....
I think the blue part is where i am doing something wrong?..
is there a simplier way to achieve this???



Sheet1 Sheet2 D E A B C row 3 13 Days 1 row 1 7 Days 7 Days 1 13 Days 1 7 Days 1 13 Days 1 7 Days 1 13 Days 1 7 Days 1 13 Days 1 7 Days 1 13 Days 1 8 Days 8 Days 1 13 Days 1 8 Days 1 13 Days 1 8 Days 1 13 Days 1 8 Days 1 13 Days 1 8 Days 1 13 Days 1 8 Days 1 13 Days 1 8 Days 1 13 Days 1 9 Days 9 Days 1 13 Days 1 9 Days 1 13 Days 1 9 Days 1 13 Days 1 9 Days 1 13 Days 1 9 Days 1 13 Days 1 9 Days 1 13 Days 1 9 Days 1 13 Days 1 9 Days 1 13 Days 1 9 Days 1 13 Days 1 10 Days 10 Days 1 13 Days 1 10 Days 1 13 Days 1 10 Days 1 13 Days 1 10 Days 1 13 Days 1 11 Days 11 Days 1 13 Days 1 11 Days 1 13 Days 1 11 Days 1 13 Days 1 11 Days 1 13 Days 1 11 Days 1 13 Days 1 11 Days 1 13 Days 1 12 Days 12 Days 1 13 Days 1 12 Days 1 13 Days 1 12 Days 1 13 Days 1 12 Days 1 13 Days 1 12 Days 1 13 Days 1 12 Days 1 13 Days 1 13 Days 13 Days 1 1 13 Days 1 1 13 Days 1 1 13 Days 1 1 13 Days 1 1 13 Days 1 1 13 Days 1


I am trying to calculate the number of days between a prior date and today, however, I want Excel to return a blank cell if the date is missing. I am calculating the number of days orders have been open (0-20, 21-30, 31-60, 61-90, and 90+). One order may have multiple items and the way I have the worksheet laid out is one row per part number. Each item on an order will all have the same document date - here is where my problem comes in. When I add the formula it calculates each item instead of consolidating them on the order number level. I want to be able to SUM the values in the aging columns to get a total number of orders and then break that down by how many orders in each aging category. In order to get this my logic says to delete the DOC DATE and DAYS OUT formulas from all but the first line of an order. So far I have been unable to get Excel to calculate the 0-20 days column correctly. I have attached a copy of the SS.

this is the formula that I am currently using.
=DATEDIF(C179,'[allocation report 2-2-11.xls]Sheet2'!$A$1,"d") <---- this is the formula in col E to return the number of days since the order was opened.

This is the one currently in col G "0-21 days " <--- when the date field is blank this returns a value of 40577 which is causing this formula to put this in the 90+ column.
=IF(OR(E178=0,E178>=21),"","1")

to recap here is what I am wanting to see:
1. if the date cell (C) is blank I want cell (E) {datedif form} to be blank
2. if the date cell (C) is blank I want cell (G) {0-21 days} to also be blank
3. if col G is SUMMED I want to see how many orders are in that category - same for columns H, I, J, and K
4. I also want to SUM col G:K to give me the total number of open orders

Can somebody pls help making a receivable aging schedule?
My data is in this format

Vendor Opening balance Receivable in current month Closing balance

I need following:

Vndr Op.bal Rec.current mo. clos.bal 0-31 days 31-60days 61-90days >91days