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!

________________________________________

(p): (518) 857-1221

Tags:

• ✭✭✭✭✭✭

Good Morning

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

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

Hope this helps

• ✭✭✭✭

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!

________________________________________

(p): (518) 857-1221

• ✭✭✭✭✭✭

Good Morning

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

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

Hope this helps

• ✭✭✭✭

Hello @Antonio Figueroa

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

-Edward

________________________________________