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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!