Time Formula

Melissa Kesler

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

Melissa Kesler

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

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

CanadaJim

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

Jim

 

Melissa Kesler

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

CanadaJim

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

Melissa Kesler

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

 

CanadaJim

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

Jim

Melissa Kesler

We use a 24 hour clock.

 

Melissa 

CanadaJim

Great!  Then did the formula above work?