+ Reply to Thread
Results 1 to 5 of 5

Aging Report

  1. #1
    Registered User
    Join Date
    03-07-2011
    Location
    qatar
    MS-Off Ver
    Excel 2007
    Posts
    12

    Aging Report

    Hi every one... Can you help me in making formula of my Aging Report. I attached already the file for editing. Any kind of effort is very much appreciated. Thank you so much. More power to this site..
    Attached Files Attached Files
    Last edited by relats; 03-09-2011 at 04:31 AM.

  2. #2
    Registered User
    Join Date
    07-01-2010
    Location
    High Wycombe
    MS-Off Ver
    Excel 2003
    Posts
    95

    Re: Aging Report

    could you be more specific in what you need so we can input the correct formula

  3. #3
    Registered User
    Join Date
    03-07-2011
    Location
    qatar
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Aging Report

    Actually I want the amount will be aged per column according to 0-30 days, 31-60 days 61-90 and so forth. Additionally, due amount beyond 365 days will be aged per month like 13-80 months and so forth. Furthermore, number days are already calculated in column F.

    Herewith re-attached file as sample. I put the amount in column according to aging manually. I just want to make it in formula so that it would hugely lessen my manual work. Thank you so much my friend.
    Attached Files Attached Files

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,452

    Re: Aging Report

    You can put this in G4 and copy to G13 and drag over to col J

    =IF(AND($F4>(COLUMNS($G:G)-1)*30,$F4<COLUMNS($G:G)*30+1),$E4,"")

    In K4 put this and drag over to M4 then copy down to row 13

    =IF(AND($F4>LEFT(K$3,3)-1,$F4<RIGHT(K$3,3)+1),$E4,"")

    I'm leaving the last 3 columns (months) to you so you can decide what # of days to assign. ie how many days for 18 months?

    In N4 and copied down you would have:

    =IF(AND($E$3>=EDATE($C4,12),$E$3<=EDATE($C4,18)),$E4,"")

    In O4 and copied down

    =IF(AND($E$3>EDATE($C4,18),$E$3<=EDATE($C4,24)),$E4,"")

    and in P4 and copied down

    =IF($E$3>EDATE($C4,24),$E4,"")
    Last edited by Cutter; 03-07-2011 at 11:33 AM.

  5. #5
    Registered User
    Join Date
    03-07-2011
    Location
    qatar
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Aging Report

    Oh what a wonderful endeavor you had been to me. My problem is already solved 99.9% but it is actually enough for me. But if you need a revisions regarding months this is it 18months = 546days, 24months =730 days and 24months =731 days and above. Thank you for the sharing knowledge with my sincere appreciation for your efforts. May God bless this site and especially to you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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