checkbox to incorporate overtime and time and a half

[madman411]

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!

[comment]

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.

[madman411]

comment, you're awesome as always!

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

[comment]

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.

[madman411]

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

[madman411]

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?

[Rick Whitelaw]

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.

[comment]

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

[madman411]

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.

[madman411]

anyone?

[comment]

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?

[madman411]

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

[comment]

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 )

)

[madman411]

hi comment

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

thank you so much! :)