Formula for Aging in days

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Formula for Aging in days

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula for Aging in days

     
    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)

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,743
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Aging in days

    Welcome to the board...

    If A1 = Invoice Date

    =TODAY()-A1
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    New Member
    Join Date
    Jan 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Aging in days

    Thanks Jonmo1 - was looking for more comprehensive formula like - we have the aging days based on ur formula below. How do we split it further in ranges of say 0-30 days, 31-60 days, 61-90 days, 91-120 days and > 120 days (data to be split based on aging days into different columns using either IF formula specifying ranges...?)

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,743
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Aging in days

    Quote Originally Posted by dominic_aca View Post
    Thanks Jonmo1 - was looking for more comprehensive formula like - we have the aging days based on ur formula below. How do we split it further in ranges of say 0-30 days, 31-60 days, 61-90 days, 91-120 days and > 120 days (data to be split based on aging days into different columns using either IF formula specifying ranges...?)
    Well, you didn't say that. All I got from the original post is, how to find the number of days between Invoice Date and Today's date...

    But anyway...

    You can try lookup

    If B1 is your basic =TODAY()-A1

    Then you can use Lookup

    =LOOKUP(B1,{0,31,61,91,121},{"0-30","31-60","61-90","91-120",">120"})
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    New Member
    Join Date
    Oct 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Aging in days

    thank you very much, it was a smart way...

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,743
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Aging in days

    Welcome to the board.

    Glad someone found this helpfull.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    New Member
    Join Date
    Nov 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Aging in days

    Hi, how to include negative value from reference cell while applying the above formula.

    Regards

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,743
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Aging in days

      
    Add the negatives to the left end of each array..
    The key is for the first array to be sorted ascending left to right.

    =LOOKUP(B1,{-30,0,31,61,91,121},{"-30--1","0-30","31-60","61-90","91-120",">120"})
    Last edited by Jonmo1; Nov 14th, 2017 at 09:53 AM.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com