removing text from cell and calculating in formula

Options

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 ✓
    Options

    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!

    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!