Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Time Formula

Hi there,

I need a formula that will round UP time, for example if I have 2.5 hours worked I would need the column to round up to 3 hours. 

I have an hours worked column - 2.5, and a column of time paid (which needs to round up to the next hour, in this case again it would be 3). 


Melissa K



  • I should star more clearly there is a clock in time column and a clock out time column - for the total I need this to round up to the next hour. 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭


    There is the ROUND() function. Typically ROUNDing means 1.5 = 2 and 1.49 = 1. Or 15 = 20 when rounding to the nearest 10 but 14 rounds to 10.

    If your data is not in hours but in clock time (2.5 vs 2:30), then that is a different thing.

    I hope this helps.


  • Also, If you *always* want to round UP, just add 0.5 to integer values before rounding.



  • Since it's in time what formula should I use, I won't always round up to 0.5...I will be summing a column an only if it equals 12.5 hours for example I need it to round up to 13 hours. But if the total was 12 hours I need it to remain. But if it's 12 hours and 15 mins I need it to round up to 13 hours, etc.




  • Hi Melissa,

    Assuming (a) that you won't have anyone entering any more than 2 decimal places (i.e. #.## hours like 1.25), and (b) that the hours are typed in using decimals (i.e. not #:##), then the following formula should work in the Hours Paid column.

    =ROUND([Hours Worked]2 + 0.49)

    In this case, because I'm using 0.49, here are the ways that different values will be rounded:

    1 -> 1

    1.001 -> 1

    1.01 -> 2

    1.99 -> 2

    1.9999999 -> 2

    2 -> 2

    If you are entering the time worked in hours and minutes (#:##), then things become much more complex.

    Hope this helps!


  • Great! I'll try this but now my formula to get the hours worked isn't working: Example I need to subtract clock out time from clock in time: =[Clock Out]3 - [Clock In]3 - I keep getting INVALID OPERATION.




  • OK, so you are probably using clock in such as 9:25 and clock out such as 10:42.  That means you need to convert the times to values.

    Is there AM/PM in the text?  Or do you use 24-hour clock times?  Or just assume that if the hour is less than 8 it's PM and if it's 8 or more then it's am?  I wrote a formula for 24-hour time, that you can modify to meet your needs for the clock times.

    =((VALUE(LEFT([Clock Out]3, FIND(":", [Clock Out]3) - 1)) - VALUE(LEFT([Clock In]3, FIND(":", [Clock In]3) - 1))) * 60 + VALUE(RIGHT([Clock Out]3, 2)) - VALUE(RIGHT([Clock In]3, 2))) / 60


  • We use a 24 hour clock.



  • Great!  Then did the formula above work?

This discussion has been closed.