Extracting text from a string and adding it to a date to get a date in the future

James Tontarski

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.

Carson Penticuff

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)

Carson Penticuff

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)

James Tontarski

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!