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
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!