Thread: Round Time with Conditions

I am in an unusual predicament. At my job they calculate time by 1/3 hours.
I'v created an Excel spreadsheet to calculate my hours with the ROUND
function. The problem is that when Excel rounds to 1/3 of hours, it isn't
the same as my jobs calculation of a third of an hour. Here's what I mean:
Job calculations
1-10 min after an hour = 0 hour
11-30 min after an hour = 1/3 of an hour
31 - 50 min after an hour = 2/3 of an hour
51 - 59 min after an hour = 1 full hour

Excel calculations
1 - 9 min after an hour= 0 hour
10 - 29 min after an hour = 1/3 of an hour
30 - 50 min after an hour = 2/3 of an hour
51 - 59 min after an hour = 1 full hour
So as you can see, Excel is one minute off up until 50 minutes and the it is
right on target. My spreadsheet is set up as follows:
Hours
Time In 12:00 Total
Time Out 19:30 7:30
MEAL BREAK
Hours
Time In 13:00 Total
Time Out 14:00 1:00
Total 6:30
TSTotal 6.666666667

So what I'm trying to do is get the TSTotal to round off with the criteria
set by my job. Help is much needed and greatly appreciated here.
Thanks
Kim

KimLL wrote:
> I am in an unusual predicament. At my job they calculate time by 1/3 hours.
> I'v created an Excel spreadsheet to calculate my hours with the ROUND
> function. The problem is that when Excel rounds to 1/3 of hours, it isn't
> the same as my jobs calculation of a third of an hour. Here's what I mean:
> Job calculations
> 1-10 min after an hour = 0 hour
> Thanks
> Kim

I'm not sure if this may be your problem, but have you allowed for
rounding _errors_ in the floating point arithmetic? i.e. you might want
to try something along the lines of
ROUND(C40+0.000001,2)
The point is to add some very small number to the argument to be
rounded, to be sure it goes into the desired interval.

John