Adding Days to Date but Days Column Includes Text
I'm trying to calculate the end-date for a purchase order.
Columns used in the formula are Start Date, Contract Max Length (Days), and I'm adding an additional 60 to cover a Net 60 payment cycle.
The "Contract Max Length (Days)" column includes a Data Verified text option in the MS Form collecting the data.
I was able to use IFERROR to calculate the total price and it worked: =IFERROR((([Bill Rate]@row * 10) * [Contract Max Length (in days)]@row), ([Bill Rate]@row))
When I use the same IFERROR formula to parenthetically add the values ([Bill Rate]@row + [Contract Max Length (in days)]@row +60), the text is "added" to the date. It doesn't cause an error, but it doesn't create a date. Ex. "10/31/22Direct Hire60"
Any advice on this one?
Answers
-
Are you able to provide some screenshots for context?
-
Screenshot for assistance
-
I would use "isnumber".
=if(isnumber([Contract Max Lenght (in days)]@row),[Start Date]@row+[Contract Max Lenght (in days)]@row+60,[Start Date]@row+60)
-
You will need to use a basic IF statement. I would suggest something like this:
=[Start Date]@row + IF(ISNUMBER([Contract Max Length (in days)]@row), [Contract Max Length (in days)]@row, 60)
In Smartsheet you can concatenate text/number/other data formats together into a string using "+". So Smartsheet thinks that you are just wanting to add the text string on to the value in the start date column. Since that is not technically an error, the IFERROR will not actually trigger.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!