removing text from cell and calculating in formula


i want to remove the text "months" from its numerical adjacent number and then calculate it against billable hours.

ex #1: month (remove "months from cell") *rate of pay*alloted hours/year

should read as 6*54*(2000/12)

i used this formula, but i get #invalid value in return =VALUE(LEFT(DURATION@row, -6)) * [HOURLY BILL RATE (USD)]@row * (2080 / 12)

however, if i use this formula it works =VALUE(LEFT(DURATION@row, 2)) * [HOURLY BILL RATE (USD)]@row * (2080 / 12) it calculates for a 2 digit month (ex: 11), but not a one digit month (ex: 4). Note, months selection is from a form selected by the person filling it out.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Lucy Steele

    We can add an IFERROR statement around your VALUE() portion of the formula, so when it errors with 2 digit months (meaning there's only 1 digit), we tell it just to grab the 1 character on the left instead of 2.

    Try this:

    =IFERROR(VALUE(LEFT(DURATION@row, 2)), VALUE(LEFT(DURATION@row, 1))) * [HOURLY BILL RATE (USD)]@row * (2080 / 12)

    Let me know if that makes sense and works for you!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!