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!


________________________________________

Edward Nadareski

(e): ejnadareski@mmhayes.com

(p): (518) 857-1221

Tags:

Best Answer

Answers

  • Edward Nadareski
    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!


    ________________________________________

    Edward Nadareski

    (e): ejnadareski@mmhayes.com

    (p): (518) 857-1221

  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭✭
    Answer ✓

    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
    Edward Nadareski ✭✭✭✭

    Hello @Antonio Figueroa

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

    -Edward


    ________________________________________

    Edward Nadareski

    (e): ejnadareski@mmhayes.com

    (p): (518) 857-1221

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!