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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!