+ Reply to Thread
Results 1 to 3 of 3

formul help to calcaulte the aging with two column under criteria

  1. #1
    Forum Contributor
    Join Date
    07-23-2013
    Location
    bangalore
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    143

    formul help to calcaulte the aging with two column under criteria

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

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: formul help to calcaulte the aging with two column under criteria

    hi johnodys. you can set up a table somewhere say in AF1:AG7 as such

    0 0-2 Days
    3 3-5 Days
    6 6-10 Days
    11 11-15 Days
    16 16-20 Days
    21 21-30 Days
    31 >30 Days


    then in AC2,
    =IF(AA2="","BLANK",LOOKUP(IF(AB2="",TODAY()-AA2,AA2-AB2),$AF$1:$AF$7,$AG$1:$AG$7))
    or if you don't want the table, then:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by benishiryo; 09-13-2013 at 09:29 AM. Reason: made a clearer presentation

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    07-23-2013
    Location
    bangalore
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    143

    Re: formul help to calcaulte the aging with two column under criteria

    Dude It works!!!wow benishiryo
    Last edited by johnodys; 09-13-2013 at 12:20 PM. Reason: thanks giving

+ 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. [SOLVED] Aging calculate with column a or b depends upon condition
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-03-2013, 01:28 PM
  2. Replies: 8
    Last Post: 07-11-2013, 01:53 AM
  3. [SOLVED] aging a date in column A unless there is a value in column B
    By tylerf in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-05-2013, 01:39 PM
  4. Using SUMIF function with multiple criteria for Aging
    By josnah in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-04-2006, 05:25 AM
  5. [SOLVED] Format Excel column as Upper Case without use of UPPERCASE formul
    By Trickywoo in forum Excel General
    Replies: 1
    Last Post: 02-05-2006, 01:55 PM

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