# Logic to calculate accurate Ageing (Aging) by Excel formula

1. ## Logic to calculate accurate Ageing (Aging) by Excel formula

I have a column A for "Created Date" and I want to calculate the accurate ageing/aging.

Created Date
07/30/2018 03:27:59
07/30/2018 06:35:32
08/29/2018 02:11:17
08/29/2018 04:20:50
08/29/2018 05:28:15
08/29/2018 06:41:57

I have found three formulas, but I am not sure which is the most accurate:

``Please Login or Register  to view this content.``
Actually, I did a comparison with above results in all three columns, then also did another comparison by putting the above formulas (separate other 3 columns) in the groups function as below:
``Please Login or Register  to view this content.``
I also googled many blogs along with cpearson
But, now I am confused. Please assist.

2. ## Re: Logic to calculate accurate Ageing (Aging) by Excel formula

You have to define what "aging" should represent.

Is it in days? hours, minutes and seconds? Is it in business hours only? Do you consider holidays? etc.

Once, you have the definition of "aging" that suites your business need... then we can help you get there by using various formula.

3. ## Re: Logic to calculate accurate Ageing (Aging) by Excel formula

At this time we are consider 24/7... means all the calendar days, and the data comes in the given date with time format in column A (given in my post above)

I want to populate three kind of results as below (in separate new columns):
(1) in the format of days only

(2) in the format of like this string --> "6 days, 18 hours, 59 minutes and 32 seconds"
Below is the formula (where column A is for Created Date and Column N for Closed Date), and I have been using it but don not know better formula (when using received Closed date or Today() or Now() functions):
``Please Login or Register  to view this content.``
(3) in the format of groups of "0-30 days", "31-60 days", "61-90 days", "91-120 days" and "120 days+"

4. ## Re: Logic to calculate accurate Ageing (Aging) by Excel formula

1) =DAYS(Closed date, Created Date)
2) That is how I'd do it via formula.
3) =LOOKUP([Column holding aging in days],{0,30,60,90,120,999},{"0-30 days","31-60 days","61-90 days","91-120 days","120 days+"})

5. ## Re: Logic to calculate accurate Ageing (Aging) by Excel formula

Originally Posted by SunOffice
I have found three formulas, but I am not sure which is the most accurate:
``Please Login or Register  to view this content.``
They are all "accurate". It just depends on what "precision" you want.

First, there is no difference between the formulas in B2 and C2. You should use C2.

PS.... There is also no difference between DAYS(TODAY(),A2) and TODAY()-A2 for valid dates in A2. Again, I would use C2, especially since DAYS() is not available before Excel 2016, and perhaps not even in standalone Excel 2016. (I don't know.)

The difference between the formulas in C2 and D2 is: Excel NOW() is TODAY() plus current time of day (truncated to the 1/100th second).

If your unit of aging is "days", as indicated, I would ignore time of day, both in the original data and in today's date. Ergo, I would use the formula in C2, not D2.

6. ## Re: Logic to calculate accurate Ageing (Aging) by Excel formula

Originally Posted by SunOffice
``Please Login or Register  to view this content.``
Another way:

=INT(A2-N2) & " days, " & TEXT(A2-N2, "h "" hours, "" m "" minutes and "" s "" seconds""")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1