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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 409 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!