Enter invoice amount in appropriate column according to aging

kmelka

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

Jeff Reisman

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.

Jeff Reisman

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.

kmelka

Thank you!!!!