Question on Value function

Hello.

Using this formula, =IFERROR(VALUE(LEFT([Time to Close (Bus Hours)]@row, 3)), IFERROR(VALUE(LEFT([Time to Close (Bus Hours)]@row, 2)),VALUE(LEFT([Time to Close (Bus Hours)@row, 1))))

I'd like to be able to take a string that contains hours and minutes, separated by a decimal point - for example 259.04 would give me 259 hours and 4 minutes, and convert it to a value of 259.04 to allow other calculations to be done.

However, I'd also like to ask, is there a way to test without getting errors, to see if I have 1, 2, or 3 digits in the hours. For example, the above formula is an attempt to check for 3 digits. If digit 3 is blank or empty (error condition), I'd like to check to see if digit 2 existed. If digit 2 is blank or empty, I'd like to check the value of digit 1. If all three digits are blank, then I have a time with only minutes. In this last example, it may look like this, 0.34 representing 34 minutes.

Anny help or suggestions would be greatly appreciated...Thank you!

Find more posts tagged with

Formulas

Accepted answers

Antonio Figueroa

Good Morning

The formula below will give you the number of characters before the "."

=VALUE(RIGHT(FIND(".", Hours@row), 5)) - 1

Hope this helps

All comments

Edward Nadareski

Hello.

Here is another approach I tired. This produces the same error condition (#UNPARSABLE).

=IF([Time to First Activity (Bus Hours)]@row <> "", IFERROR(VALUE(LEFT([Time to Close (Bus Hours)]@row, 2)), IFERROR(VALUE(LEFT([Time to Close (Bus Hours)]@row, 1)),VALUE(LEFT([Time to Close (Bus Hours)@row,3)))))

In this case, I am attempting to ensure I have a valid value to start my analysis with (Time to First Activity (Bus Hours) is not 0).

Thank you!

Antonio Figueroa

Good Morning

The formula below will give you the number of characters before the "."

=VALUE(RIGHT(FIND(".", Hours@row), 5)) - 1

Hope this helps

Edward Nadareski

Hello @Antonio Figueroa

Thank you very much for the great suggestion. This did help!

-Edward

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.