removing text from cell and calculating in formula

Lucy Steele

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.

Genevieve P.

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

Genevieve P.

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

Lucy Steele

Hi @Genevieve P. ,

Thank you so much for your help, it worked perfectly.

Cheers,

Lucy