+ Reply to Thread
Results 1 to 12 of 12

creating an aging report

  1. #1
    PSikes
    Guest

    creating an aging report

    Hi,
    I'm trying to create an Accounts Receivable "aging report", using Excel
    2003. I've got a field (a date field), that provides the date of invoice.
    I'd like then to add 30, 60, or 90 days from that date, and call that the
    "Due Date" for payment in another column.

    Thanks,
    Peter


  2. #2
    Peo Sjoblom
    Guest

    Re: creating an aging report

    Excel dates are just numbers so if your invoice is in a date format that
    excel recognizes just add

    =A1+30
    =A1+60


    and do on where A1 holds the date

    --
    Regards,

    Peo Sjoblom

    (No private emails please)


    "PSikes" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    > I'm trying to create an Accounts Receivable "aging report", using Excel
    > 2003. I've got a field (a date field), that provides the date of invoice.
    > I'd like then to add 30, 60, or 90 days from that date, and call that the
    > "Due Date" for payment in another column.
    >
    > Thanks,
    > Peter
    >



  3. #3
    Ron Coderre
    Guest

    RE: creating an aging report

    See if this example is gets you pointed in the right direction:

    A1: DueDate
    B1: InvAmt
    C1: 30_Days
    D1: 60_Days
    E1: 90_Days
    F1: Over_90

    A2: (some date)
    B2: (an invoice amount)
    C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"")
    D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"")
    E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"")
    F2: =IF(TRUNC(($A2-TODAY())/30)>=3,$B2,"")

    (copy the formulas in C2 thru F2 down as far as you need)

    Does that help?

    ***********
    Regards,
    Ron

    XL2002, WinXP-Pro


    "PSikes" wrote:

    > Hi,
    > I'm trying to create an Accounts Receivable "aging report", using Excel
    > 2003. I've got a field (a date field), that provides the date of invoice.
    > I'd like then to add 30, 60, or 90 days from that date, and call that the
    > "Due Date" for payment in another column.
    >
    > Thanks,
    > Peter
    >


  4. #4
    PSikes
    Guest

    Re: creating an aging report



    "Peo Sjoblom" wrote:

    > Excel dates are just numbers so if your invoice is in a date format that
    > excel recognizes just add
    >
    > =A1+30
    > =A1+60
    >
    >
    > and do on where A1 holds the date
    >
    > --
    > Regards,
    >
    > Peo Sjoblom
    >
    > (No private emails please)
    >
    >
    > "PSikes" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi,
    > > I'm trying to create an Accounts Receivable "aging report", using Excel
    > > 2003. I've got a field (a date field), that provides the date of invoice.
    > > I'd like then to add 30, 60, or 90 days from that date, and call that the
    > > "Due Date" for payment in another column.
    > >
    > > Thanks,
    > > Peter
    > >

    >
    > Dang, that was easy. Thanks Peo.

    Peter


  5. #5
    PSikes
    Guest

    RE: creating an aging report



    "Ron Coderre" wrote:

    > See if this example is gets you pointed in the right direction:
    >
    > A1: DueDate
    > B1: InvAmt
    > C1: 30_Days
    > D1: 60_Days
    > E1: 90_Days
    > F1: Over_90
    >
    > A2: (some date)
    > B2: (an invoice amount)
    > C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"")
    > D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"")
    > E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"")
    > F2: =IF(TRUNC(($A2-TODAY())/30)>=3,$B2,"")
    >
    > (copy the formulas in C2 thru F2 down as far as you need)
    >
    > Does that help?
    >
    > ***********
    > Regards,
    > Ron
    >
    > XL2002, WinXP-Pro
    >
    >
    > "PSikes" wrote:
    >
    > > Hi,
    > > I'm trying to create an Accounts Receivable "aging report", using Excel
    > > 2003. I've got a field (a date field), that provides the date of invoice.
    > > I'd like then to add 30, 60, or 90 days from that date, and call that the
    > > "Due Date" for payment in another column.
    > >
    > > Thanks,
    > > Peter



    > > Excellent!

    Thanks Ron. If I set up my entry worksheet, I can create a little Pivot
    table that'll key off of all 3 (30, 60, 90) categories -- just like the more
    expensive accounting programs do.
    Thanks much,
    Peter


  6. #6
    Robert
    Guest

    RE: creating an aging report

    Peter,

    Would you mind sharing your pivot table and entry worksheet> I am trying to
    do this also.

    Thanks

    Robert

    "PSikes" wrote:

    >
    >
    > "Ron Coderre" wrote:
    >
    > > See if this example is gets you pointed in the right direction:
    > >
    > > A1: DueDate
    > > B1: InvAmt
    > > C1: 30_Days
    > > D1: 60_Days
    > > E1: 90_Days
    > > F1: Over_90
    > >
    > > A2: (some date)
    > > B2: (an invoice amount)
    > > C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"")
    > > D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"")
    > > E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"")
    > > F2: =IF(TRUNC(($A2-TODAY())/30)>=3,$B2,"")
    > >
    > > (copy the formulas in C2 thru F2 down as far as you need)
    > >
    > > Does that help?
    > >
    > > ***********
    > > Regards,
    > > Ron
    > >
    > > XL2002, WinXP-Pro
    > >
    > >
    > > "PSikes" wrote:
    > >
    > > > Hi,
    > > > I'm trying to create an Accounts Receivable "aging report", using Excel
    > > > 2003. I've got a field (a date field), that provides the date of invoice.
    > > > I'd like then to add 30, 60, or 90 days from that date, and call that the
    > > > "Due Date" for payment in another column.
    > > >
    > > > Thanks,
    > > > Peter

    >
    >
    > > > Excellent!

    > Thanks Ron. If I set up my entry worksheet, I can create a little Pivot
    > table that'll key off of all 3 (30, 60, 90) categories -- just like the more
    > expensive accounting programs do.
    > Thanks much,
    > Peter
    >


  7. #7
    PSikes
    Guest

    RE: creating an aging report

    Hi Robert,
    Sure, be glad to. Give me a few. I'm redesigning the entry worksheet,
    mostly to get rid of a lot of unecessary fat. A digitally appropriate New
    Year's resolution, don't you think?
    Peter


    "Robert" wrote:

    > Peter,
    >
    > Would you mind sharing your pivot table and entry worksheet> I am trying to
    > do this also.
    >
    > Thanks
    >
    > Robert
    >
    > "PSikes" wrote:
    >
    > >
    > >
    > > "Ron Coderre" wrote:
    > >
    > > > See if this example is gets you pointed in the right direction:
    > > >
    > > > A1: DueDate
    > > > B1: InvAmt
    > > > C1: 30_Days
    > > > D1: 60_Days
    > > > E1: 90_Days
    > > > F1: Over_90
    > > >
    > > > A2: (some date)
    > > > B2: (an invoice amount)
    > > > C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"")
    > > > D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"")
    > > > E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"")
    > > > F2: =IF(TRUNC(($A2-TODAY())/30)>=3,$B2,"")
    > > >
    > > > (copy the formulas in C2 thru F2 down as far as you need)
    > > >
    > > > Does that help?
    > > >
    > > > ***********
    > > > Regards,
    > > > Ron
    > > >
    > > > XL2002, WinXP-Pro
    > > >
    > > >
    > > > "PSikes" wrote:
    > > >
    > > > > Hi,
    > > > > I'm trying to create an Accounts Receivable "aging report", using Excel
    > > > > 2003. I've got a field (a date field), that provides the date of invoice.
    > > > > I'd like then to add 30, 60, or 90 days from that date, and call that the
    > > > > "Due Date" for payment in another column.
    > > > >
    > > > > Thanks,
    > > > > Peter

    > >
    > >
    > > > > Excellent!

    > > Thanks Ron. If I set up my entry worksheet, I can create a little Pivot
    > > table that'll key off of all 3 (30, 60, 90) categories -- just like the more
    > > expensive accounting programs do.
    > > Thanks much,
    > > Peter
    > >


  8. #8
    Robert
    Guest

    RE: creating an aging report

    It certainly would.....

    Robert

    "PSikes" wrote:

    > Hi Robert,
    > Sure, be glad to. Give me a few. I'm redesigning the entry worksheet,
    > mostly to get rid of a lot of unecessary fat. A digitally appropriate New
    > Year's resolution, don't you think?
    > Peter
    >
    >
    > "Robert" wrote:
    >
    > > Peter,
    > >
    > > Would you mind sharing your pivot table and entry worksheet> I am trying to
    > > do this also.
    > >
    > > Thanks
    > >
    > > Robert
    > >
    > > "PSikes" wrote:
    > >
    > > >
    > > >
    > > > "Ron Coderre" wrote:
    > > >
    > > > > See if this example is gets you pointed in the right direction:
    > > > >
    > > > > A1: DueDate
    > > > > B1: InvAmt
    > > > > C1: 30_Days
    > > > > D1: 60_Days
    > > > > E1: 90_Days
    > > > > F1: Over_90
    > > > >
    > > > > A2: (some date)
    > > > > B2: (an invoice amount)
    > > > > C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"")
    > > > > D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"")
    > > > > E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"")
    > > > > F2: =IF(TRUNC(($A2-TODAY())/30)>=3,$B2,"")
    > > > >
    > > > > (copy the formulas in C2 thru F2 down as far as you need)
    > > > >
    > > > > Does that help?
    > > > >
    > > > > ***********
    > > > > Regards,
    > > > > Ron
    > > > >
    > > > > XL2002, WinXP-Pro
    > > > >
    > > > >
    > > > > "PSikes" wrote:
    > > > >
    > > > > > Hi,
    > > > > > I'm trying to create an Accounts Receivable "aging report", using Excel
    > > > > > 2003. I've got a field (a date field), that provides the date of invoice.
    > > > > > I'd like then to add 30, 60, or 90 days from that date, and call that the
    > > > > > "Due Date" for payment in another column.
    > > > > >
    > > > > > Thanks,
    > > > > > Peter
    > > >
    > > >
    > > > > > Excellent!
    > > > Thanks Ron. If I set up my entry worksheet, I can create a little Pivot
    > > > table that'll key off of all 3 (30, 60, 90) categories -- just like the more
    > > > expensive accounting programs do.
    > > > Thanks much,
    > > > Peter
    > > >


  9. #9
    PSikes
    Guest

    RE: creating an aging report

    Ron,

    I've had a chance to try out your sample. If I understand the script
    correctly, I'll not only get a clear snapshot of overdue collections within
    the 30,60,and 90 day timeframes, but I can total them up too! For some
    reason, the formula doesn't seem to work over the 2005-2006 window though.
    If I date an Due date back in November 1 2005 (not so unusual in today's
    environment), for instance -- using the TODAY date in the formula. The
    invoice amount doesn't show up in the 60 day window that I'd expect to see
    it. Did I do something wrong?

    Peter
    "Ron Coderre" wrote:

    > See if this example is gets you pointed in the right direction:
    >
    > A1: DueDate
    > B1: InvAmt
    > C1: 30_Days
    > D1: 60_Days
    > E1: 90_Days
    > F1: Over_90
    >
    > A2: (some date)
    > B2: (an invoice amount)
    > C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"")
    > D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"")
    > E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"")
    > F2: =IF(TRUNC(($A2-TODAY())/30)>=3,$B2,"")
    >
    > (copy the formulas in C2 thru F2 down as far as you need)
    >
    > Does that help?
    >
    > ***********
    > Regards,
    > Ron
    >
    > XL2002, WinXP-Pro
    >
    >
    > "PSikes" wrote:
    >
    > > Hi,
    > > I'm trying to create an Accounts Receivable "aging report", using Excel
    > > 2003. I've got a field (a date field), that provides the date of invoice.
    > > I'd like then to add 30, 60, or 90 days from that date, and call that the
    > > "Due Date" for payment in another column.
    > >
    > > Thanks,
    > > Peter
    > >


  10. #10
    PSikes
    Guest

    RE: creating an aging report

    Robert,

    Waiting an answer on my last post. The program that Ron suggested works,
    but apparently not over the span of a year.
    Peter


    "Robert" wrote:

    > Peter,
    >
    > Would you mind sharing your pivot table and entry worksheet> I am trying to
    > do this also.
    >
    > Thanks
    >
    > Robert
    >
    > "PSikes" wrote:
    >
    > >
    > >
    > > "Ron Coderre" wrote:
    > >
    > > > See if this example is gets you pointed in the right direction:
    > > >
    > > > A1: DueDate
    > > > B1: InvAmt
    > > > C1: 30_Days
    > > > D1: 60_Days
    > > > E1: 90_Days
    > > > F1: Over_90
    > > >
    > > > A2: (some date)
    > > > B2: (an invoice amount)
    > > > C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"")
    > > > D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"")
    > > > E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"")
    > > > F2: =IF(TRUNC(($A2-TODAY())/30)>=3,$B2,"")
    > > >
    > > > (copy the formulas in C2 thru F2 down as far as you need)
    > > >
    > > > Does that help?
    > > >
    > > > ***********
    > > > Regards,
    > > > Ron
    > > >
    > > > XL2002, WinXP-Pro
    > > >
    > > >
    > > > "PSikes" wrote:
    > > >
    > > > > Hi,
    > > > > I'm trying to create an Accounts Receivable "aging report", using Excel
    > > > > 2003. I've got a field (a date field), that provides the date of invoice.
    > > > > I'd like then to add 30, 60, or 90 days from that date, and call that the
    > > > > "Due Date" for payment in another column.
    > > > >
    > > > > Thanks,
    > > > > Peter

    > >
    > >
    > > > > Excellent!

    > > Thanks Ron. If I set up my entry worksheet, I can create a little Pivot
    > > table that'll key off of all 3 (30, 60, 90) categories -- just like the more
    > > expensive accounting programs do.
    > > Thanks much,
    > > Peter
    > >


  11. #11
    Registered User
    Join Date
    08-25-2016
    Location
    Medinah,Saudi Arabia
    MS-Off Ver
    2013
    Posts
    1

    Re: creating an aging report

    How to make aging report when amount is also paid

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: creating an aging report

    @Fahad Rasheed: this is a very old thread which is long past its "sell by" date, plus you do not "hijack" another thread.

    Please start a new thread AND ....

    Attach a sample workbook.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

+ 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