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.

Find more posts tagged with

Accepted answers

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

All comments

Genevieve P.

Hi @Lucy Steele

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.