Results 1 to 6 of 6

Aging Invoice Calculator - Excel Formula SNAFU

This is a discussion on Aging Invoice Calculator - Excel Formula SNAFU within the Excel Questions forums, part of the Question Forums category; I have an excel sheet that I want to calculate total of invoices that are 31-60, 61-90 and over 90 ...

  1. #1
    New Member
    Join Date
    Aug 2003
    Location
    Florida
    Posts
    5

    Default Aging Invoice Calculator - Excel Formula SNAFU

    I have an excel sheet that I want to calculate total of invoices that are 31-60, 61-90 and over 90 days old. I can only get the formula to calculate based on what MONTH it currently is. I have a cell that contains a DATE, what I would like to do is be able to put any date in that cell and the formula calculate the results accordingly based on that date. I will follow this post with an attempt to display the sheet here in teh forum...

    Regards

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Aging Invoice Calculator - Excel Formula SNAFU

    Using a PivotTable...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    1
    InvoiceDateAmountAge*Sum*of*Amount********
    2
    1004A7/15/200310140*AgeTotal*******
    3
    1005B9/25/20031568*31-6025*******
    4
    2012B10/13/20032550*61-9015*******
    5
    *****>9010*******
    6
    *****Grand*Total50*******
    7
    **************
    8
    **************
    Sheet3*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  3. #3
    New Member
    Join Date
    Aug 2003
    Location
    Florida
    Posts
    5

    Default Re: Aging Invoice Calculator - Excel Formula SNAFU

    That's ALMOST IT!
    I see how it uses the TODAY in calculating, but I guess what I want to do is take the amount in a cell IF it's 31-60 days old and add it with others that meet the same criteria.

    I must be able to change the date that the formul is based on, such as not TODAY but enter any other date and calculate based on that date. That way, I can forcast what the aging report is going to look like on future dates.

    Am I confused?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Aging Invoice Calculator - Excel Formula SNAFU

    Quote Originally Posted by bristolxyz
    That's ALMOST IT!
    I see how it uses the TODAY in calculating, but I guess what I want to do is take the amount in a cell IF it's 31-60 days old and add it with others that meet the same criteria.

    I must be able to change the date that the formul is based on, such as not TODAY but enter any other date and calculate based on that date. That way, I can forcast what the aging report is going to look like on future dates.

    Am I confused?
    Simply replace the TODAY() worksheet function with a reference to a cell containing a date that you want to use in the comparison.

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Aging Invoice Calculator - Excel Formula SNAFU

    ******** ******************** ************************************************************************>
    Microsoft Excel - Aging Debtors.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    1
    **Date:3-Dec-03****
    2
    ********
    3
    Invoice*#DateAmountCurrentOver*30Over*60Over*90*
    4
    18-Jan-03$100.00*
    *
    *
    $100.00*
    5
    231-Mar-03$150.00*
    *
    *
    $150.00*
    6
    330-Jun-03$75.00*
    *
    *
    $75.00*
    7
    415-Aug-03$30.00*
    *
    *
    $30.00*
    8
    522-Aug-03$50.00*
    *
    *
    $50.00*
    9
    65-Sep-03$28.00*
    *
    $28.00*
    *
    10
    729-Sep-03$15.00*
    *
    $15.00*
    *
    11
    811-Oct-03$26.00*
    $26.00*
    *
    *
    12
    922-Oct-03$150.00*
    $150.00*
    *
    *
    13
    104-Nov-03$66.00$66.00*
    *
    *
    *
    14
    1125-Nov-03$82.00$82.00*
    *
    *
    *
    15
    121-Dec-03$55.00$55.00*
    *
    *
    *
    16
    ********
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Change the date in cell D1.

    D4: =IF($D$1-B4<=30,C4,"")
    E4: =IF(AND($D$1-B4>=31,$D$1-B4<=60),C4,"")
    F4: =IF(AND($D$1-B4>=61,$D$1-B4<=90),C4,"")
    G4: =IF($D$1-B4>90,C4,"")

    Copy down.

    You may have to tweak the formulas to get exactly what you want.

    HTH


    Mike

  6. #6
    New Member
    Join Date
    Jun 2013
    Posts
    1

    Default Re: Aging Invoice Calculator - Excel Formula SNAFU

    Ekim this is just what I need!! The only problem is I don't know how to get it to adjust the aging if a payment has been applied against the invoice. Hope 10 years later you're still around to help, or someone can

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