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

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

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

Help Article Resources
Categories
Check out the Formula Handbook template!