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).
Thanks,
Melissa K
Comments

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.

Melissa,
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.
Craig

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

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.
Thanks,
Melissa

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!
Jim

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.
Melissa

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 24hour 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 24hour 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
Jim

We use a 24 hour clock.
Melissa

Great! Then did the formula above work?