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!