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.

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.

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

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+"})

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.