Jump to content
Sign in to follow this  
madman411

checkbox to incorporate overtime and time and a half

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 this post


Link to post
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 this post


Link to post
Share on other sites

comment, you're awesome as always!

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

Share this post


Link to post
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 this post


Link to post
Share on other sites

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

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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.

Share this post


Link to post
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 this post


Link to post
Share on other sites

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

Share this post


Link to post
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 this post


Link to post
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! :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

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