Need help creating a calculation

[OffTheDeepEnd.com]

Perhaps someone has a good idea on how I can create a calculation for the time clock system our contractor uses. When they figure out our employees time cards, they base it on a decimal hour conversion table. I need to see if I can create a database that uses their system to determine their hours. The table is as follows:

Mins. | Hrs

57-02 | 0.0

03-08 | 0.1

09-14 | 0.2

15-20 | 0.3

21-26 | 0.4

27-32 | 0.5

33-38 | 0.6

39-44 | 0.7

45-50 | 0.8

51-56 | 0.9

Now what all that means is another thing. Here is an example:

Clock In: 9:45 Clock Out: 13:33

Now using the decimal hour conversion table, the times come out to:

Clock In: 9.80 hrs Clock Out: 13.60 hrs

That way all you have to do is subtract the clock in from the clock out and you get the total hours worked. How would you get filemaker to determine the times using the table? Something to think about also is if the employee clocks in at 9:58, according to the table, it would round it up to 10 hrs actually. I thought the case function would work but I cannot get it to round up the hour once it passed 56 minutes. Any Ideas?

[-Queue-]

Why not use something like Round( (ClockOut - ClockIn) / 3600, 1 ) to determine the hours to the tenth?

[djgogi]

Hour ( timeIn )+

Case ( Minute ( timeIn ) > 56 ; 1.00 ; Minute ( timeIn )>50; 0.90 ; Minute ( timeIn )>44; 0.80 ; Minute ( timeIn )>38; 0.70 ; Minute ( timeIn )>32; 0.60 ; Minute ( timeIn )>26; 0.50 ; Minute ( timeIn )>20;0.40 ; Minute ( timeIn )>14; 0.30 ; Minute ( timeIn )>8; 0.20 ; Minute ( timeIn )>2; 0.10 ;0)

Dj

[-Queue-]

Note that any time span less than an hour will yield an incorrect result. E.g. TimeIn = 9:57, TimeOut = 10:45 yields 10.8 - 9.0 = 1.8, when it should be 0.8.

[djgogi]

10:45 (=10.80)-9:57(=10.00) gives 0.80

9:57 is 10.00

He, only, didn't make it explicit in his post!

DJ

[-Queue-]

Ok then. That works. I still think it would be easier to use Round, since the fractions of 60 appear to be treated the same way.

[djgogi]

Right,

I didn't check the formula you gave him!

Dj

[djgogi]

I've just checked it.

It does not respect the coversion table:

In 14:31(=14.50)

Out 15:03(=15.10)

Diff=0.6 while your formula yields 0.5

Dj

[-Queue-]

Ah, that's because it's rounding before the calculation. Duh on me.

How about Round( cout / 3600, 1 ) - Round( cin / 3600, 1 )?

[djgogi]

That one looks rigth to me.

Dj

[OffTheDeepEnd.com]

Well, I appreciate the help, but when I went to create the fields, I have no clue what goes where.

[-Queue-]

In your field definition for Hours, enter a calculation of Round( ClockOut / 3600, 1 ) - Round( ClockIn / 3600, 1 ).