+ Reply to Thread
Results 1 to 6 of 6

Logic to calculate accurate Ageing (Aging) by Excel formula

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    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.
    Last edited by SunOffice; 09-28-2018 at 10:09 AM.
    Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    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.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    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+"
    Last edited by SunOffice; 09-28-2018 at 10:29 AM.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    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. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

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

    Quote Originally Posted by SunOffice View Post
    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.
    Last edited by joeu2004; 09-28-2018 at 12:32 PM. Reason: PS

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

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

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate the correct Ageing days
    By SBBmaster09 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2016, 07:04 AM
  2. [SOLVED] How to calculate debtors ageing amount in days on FIFO basis
    By King_BD in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-20-2015, 10:57 PM
  3. Replies: 3
    Last Post: 07-04-2014, 09:57 PM
  4. Replies: 12
    Last Post: 07-10-2013, 09:22 AM
  5. [SOLVED] TODAY function: why does it not give an accurate aging value?
    By nlh in forum Excel General
    Replies: 1
    Last Post: 05-30-2012, 10:12 AM
  6. Logic Formula to calculate distance
    By xury2000 in forum Excel General
    Replies: 4
    Last Post: 07-07-2009, 01:21 PM
  7. [SOLVED] goal seek wont calculate an accurate value past 3 decimal places
    By Joe Browning in forum Excel General
    Replies: 1
    Last Post: 04-13-2005, 03:06 AM

Tags for this Thread

Bookmarks

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