Enter invoice amount in appropriate column according to aging
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
-
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
-
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!
-
Thank you!!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!