Enter invoice amount in appropriate column according to aging

Options
kmelka
kmelka ✭✭
edited 01/26/22 in Formulas and Functions

Hello- I am trying to have the invoice amount appear in the appropriate column based on the date. So an invoice from Dec 1 would now appear in the OVER 30 day column and on Feb 1 would jump to display in the 60 day column.


=IF(CHOOSE(MATCH(TODAY()-[Invoice Date]@row,{-99999,0,31,61,91,99999},1),"Future Date","0-30 Days","31-60 Days","61-90 Days","Over 90 Days")=[0-30 Days]@row,[Amount Outstanding]@row,"")

thank you in advance,

km

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/26/22 Answer ✓
    Options

    If you're starting out with an Invoice Date column and an Amount Outstanding column, then it should be easy to use IF formulas in the OVER 30 column, OVER 60, etc., as long as your invoice date column is a date-format column and the values are actual dates.

    In your OVER 30 column:

    =IF(AND([Invoice Date]@row <TODAY(-30), [Invoice Date]@row > TODAY(-60)), [Amount Outstanding]@row, "")

    In English: If the invoice date is earlier than 30 days ago and is later than 60 days ago, show the Amount Outstanding from this row in this cell, otherwise, just leave it blank.

    In OVER 60 column:

    =IF(AND([Invoice Date]@row <TODAY(-60), [Invoice Date]@row > TODAY(-90)), [Amount Outstanding]@row, "")

    In OVER 90 column:

    =IF([Invoice Date]@row <TODAY(-90), [Amount Outstanding]@row, "")

    Note that this will not "move" the value out of the Amount Outstanding column, but will display the amount in whichever of the other columns it fits in.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/26/22 Answer ✓
    Options

    If you're starting out with an Invoice Date column and an Amount Outstanding column, then it should be easy to use IF formulas in the OVER 30 column, OVER 60, etc., as long as your invoice date column is a date-format column and the values are actual dates.

    In your OVER 30 column:

    =IF(AND([Invoice Date]@row <TODAY(-30), [Invoice Date]@row > TODAY(-60)), [Amount Outstanding]@row, "")

    In English: If the invoice date is earlier than 30 days ago and is later than 60 days ago, show the Amount Outstanding from this row in this cell, otherwise, just leave it blank.

    In OVER 60 column:

    =IF(AND([Invoice Date]@row <TODAY(-60), [Invoice Date]@row > TODAY(-90)), [Amount Outstanding]@row, "")

    In OVER 90 column:

    =IF([Invoice Date]@row <TODAY(-90), [Amount Outstanding]@row, "")

    Note that this will not "move" the value out of the Amount Outstanding column, but will display the amount in whichever of the other columns it fits in.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • kmelka
    kmelka ✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!