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



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

View Answers     

Similar Excel Tutorials

Get Formulas from Cells in Excel with VBA
Get a formula from a cell using VBA in Excel. This method returns the actual formula instead of the output value. G ...
Count the Number of Unique Values in a List in Excel
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...
How to Input, Edit, and manage Formulas and Functions in Excel
In this tutorial I am going to introduce how to input, edit and manage excel formulas. To start entering a formula, ...
Apply Conditional Formatting to Multiple Cells with a Single Formula
How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...

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









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




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

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.




Dear expert

i have aging formula to calculate with a or b depends upon condition of a and b column.

I have column in column a and b i want aging depends upon cell value below....

If a column is blank then take aging with b
if b column is blank then take agin with a
if a & b both column is blank result not found
if a & b column have date i want calculate aging with latest date.

Find the attachment

you may get understand@!!!!!!

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.


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