Extracting text from a string and adding it to a date to get a date in the future
Apologies if the title wasn't helpful  wasn't sure how to better summarize the problem.
I have two columns  Payment Terms (Dropdown) and Invoice Date (Date). The Payment Terms column is either numbers like 30, 45, or 60 OR a string that describes the discount if paid in a certain timeframe: "1%20 / NET 60". I have been asked to write a formula that provides a forecast date for making the payments. It would be the Invoice Date + the amount of dates from the Payments Terms.
I was able to find a way to add the days if the Payment Terms are just a number:
=IF(LEN([Payment Terms]@row) > 2, "01/01/2024", [Invoice Date]@row + [Payment Terms]@row).
I want to figure out the way to replace "01/01/2024" with another formula that finds the 2 digits immediately after the % sign in the Payment Term. After searching around, I figure it will be some combination of RIGHT, LEFT, and FIND functions but I cannot figure it out.
EDIT: After searching, I think the MID function could work too? Also to throw in another issue, there's a case when the Payment Terms could be "1.5%20 / NET 60", so hardcoding a count value doesn't seem like it will work.
Best Answer

You are on the right track with the MID function, give this a try:
=IF(LEN([Payment Terms]@row) > 2, [Invoice Date]@row + VALUE(MID([Payment Terms]@row, FIND("%", [Payment Terms]@row) + 1, 2)), [Invoice Date]@row + [Payment Terms]@row)
Answers

You are on the right track with the MID function, give this a try:
=IF(LEN([Payment Terms]@row) > 2, [Invoice Date]@row + VALUE(MID([Payment Terms]@row, FIND("%", [Payment Terms]@row) + 1, 2)), [Invoice Date]@row + [Payment Terms]@row)

Thanks for the response Carson. It looks like when I include the VALUE() function, it returns an error but if I put the MID() function in a helper column and then VALUE() on the helper it works!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!