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

Options
James Tontarski
James Tontarski ✭✭✭
edited 04/05/24 in Formulas and Functions

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.

Tags:

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!