Zeroes after decimal point not being extracted

Having issue with decimal numbers. I'm trying to extract the numbers after the decimals, but if it's a whole number, it doesn't recognize the zeroes and just gives the value before the decimal point. Anyone have a solution for this?

The value I want to get out of the above formula is the 00

The reason I need to extract the numbers after the decimal point is because we are putting times with decimal points instead of semi-colons( example 15:30 would be 15.30) the problem is for calculations of time I need the value in fractions of hours. For example, if an activity runs between 15.30-16.30, I'll need to convert it to 15.5-16.5 so I can calculate that the activity takes 1 hour to do. If you have another way of converting these time values, that would help me as well.


  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 07/09/21

    Whether it is a number, a date, etc, you need to convert the whatever is in that cell into a string (text) by adding "" to it before you use the string functions, RIGHT( ), LEFT( ), SUBSTITUTE( ), or MID( ).

    For example, RIGHT([Tech 6 End]@row + "", 2).

    Further...1 + 2 returns 3 but 1 + "" + 2 returns 12 . Thus, RIGHT( 1 + 2, 1) returns 3 and RIGHT( 1 + "" + 2, 1) returns 2 .

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!