## Recommended Posts

Hey guys

I have a small database which utilizes a portal where I enter my hours for each day worked. Time In, Break Out, Break In, and Time out. I then have a field that calculates the total hours worked (everything is in 12 hour time), and then another field that calculates my pay for the day based on my hourly rate which is entered in a separate field under a separate table (total hours * rate). My tables are Week Info and Hours Worked.

I'm trying to include two check boxes on each row that calculate one or both of the following: over time (time and a half for any hours exceeding 8), and time and a half (for days such as holidays where I earn time and a half for the whole day).

When either of these buttons are checked I want the total pay for the day to calculate correctly, and vise-versa where the original estimated pay will display if none of the boxes are checked.

I realize I need two potential "hidden" fields which calculate the additional pay, but I'm rather confused where and how to incorporate this feature I'm trying to add, or how to write a script for this.

Any input is appreciated. Thank you!

##### Share on other sites

Try =

```Let ( [

h = ( TimeOut - BreakIn + BreakOut - TimeIn ) / 3600

] ;

Case (

TimeAndHalf ;

h * Rate * 1.5 ;

Overtime ;

Min ( h ; 8 ) * Rate + Max ( h - 8 ; 0 ) * Rate * 1.5 ;

h * Rate )

)```

Note:

1. The two checkbox fields, TimeAndHalf and Overtime, should be defined as Number fields and use a custom value list of "1";

2. Rates tend to change, so you should lookup the current rate into a field local to the Hours Worked.table.

##### Share on other sites

comment, you're awesome as always!

would this be applied to a calculation field? if so, which one?

##### Share on other sites

Yes, this is a formula to use in a calculation field in the Hours Worked.table. The result should be a Number.

Not sure what you mean by "which one"; the above should return the total pay for the day.

##### Share on other sites

Perfect. Thank you. So this potentially replaces the calculation in my "day's pay" calculation field.

##### Share on other sites

Let ( [

h = ( TimeOut - BreakIn + BreakOut - TimeIn ) / 3600

] ;

Case (

TimeAndHalf ;

h * Rate * 1.5 ;

Overtime ;

Min ( h ; 8 ) * Rate + Max ( h - 8 ; 0 ) * Rate * 1.5 ;

h * Rate )

)

anyway to incorporate double time in to the o/t function? double time is after 12 hours. would a separate check box be necessary, or can i add it in to the existing o/t function?

##### Share on other sites

Careful! You mentioned in your original post that everything was in 12-hour time. Now you speak of double time as being over 12 hours.

##### Share on other sites

anyway to incorporate double time

Yes, it's practically the same thing as overtime - just use 12 instead of 8. Note that when using the Case() function, the order of tests is important.

Careful! You mentioned in your original post that everything was in 12-hour time. Now you speak of double time as being over 12 hours.

There is no such thing as "12-hour time".

##### Share on other sites

Yes, it's practically the same thing as overtime - just use 12 instead of 8. Note that when using the Case() function, the order of tests is important.

There is no such thing as "12-hour time".

```
Let ( [

h = ( TimeOut - BreakIn + BreakOut - TimeIn ) / 3600

] ;

Case (

TimeAndHalf ;

h * Rate * 1.5 ;

Doubletime ;

Min ( h ; 12 ) * Rate + Max ( h - 12 ; 0 ) * Rate * 2 ;

Overtime ;

Min ( h ; 8 ) * Rate + Max ( h - 8 ; 0 ) * Rate * 1.5 ;

h * Rate )

)

```

such as above? if double time and ot are selected then they counteract each other. i'm hoping for this code to calculate both time and a half and double should the hours pass 12. i.e. the four hours after 8 are * 1.5 + the hours exceeding 12 are * 2.

anyone?

##### Share on other sites

I thought this question was answered. If not, please clarify how the different criteria should interact: e.g. will double time be always applied after 12 hours, or only if indicated?

##### Share on other sites

in this case double time will always be applied after 12 hours (when OT is checked). thank you!

##### Share on other sites

I am not sure I understand that, but try =

```Let ( [

h = ( TimeOut - BreakIn + BreakOut - TimeIn ) / 3600

] ;

Case (

TimeAndHalf ;

h * Rate * 1.5 ;

Overtime ;

Let ( [

double = Max ( h - 12 ; 0 ) ;

overtime = Max ( h - double - 8 ; 0 ) ;

regular = Min ( h ; 8 )

] ;

double * Rate * 2 + overtime * Rate * 1.5 + regular * Rate

) ;

h * Rate )

)```

##### Share on other sites

hi comment

thanks for your help. i managed to edit my calculation properly with the formula you provided.

thank you so much! :)

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible. Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...

## Important Information

By using this site, you agree to our Terms of Use.