Calculate Overtime & Double Time

[emncwundy]

I'd like to create a formula that automatically calculates overtime for each working day.

 

As a standard week (or normal time NT) Monday to Thursday our employees work an 8.5 hour day and on Fridays they work 4 hours.

 

We pay time and a half (overtime OT) for the first 2 hours after the normal time and double time (DT) for anything beyond that.

 

So far in my head i have worked out that if

 

DayOfWeek = 2-5 (Mon - Thurs), then NT is 8.5 hours, and OT is any hours > 8.5 hrs, but less than 10.5 and DT is >10.5 hours

and if

DayOfWeek = 6 (Fri), then DT is 4 hours, and OT is > 4 but < 6 hrs and DT is > 6 hours

and finally, if

DayOfWeek = 7 or 1 (Sat or Sunday), then OT is 0-2 hrs and DT is >2hrs

 

Does this make sense?

 

Can anyone help me string the formula together? Its a bit much for my brain!

 

 

[Matt Malyschko]

Some more information regarding the structure of your database would be beneficial.

 

If I were to do this, I would probably have an employees table, and a working days table, when a new employee works a day, a record is added to the working days table with the day, date, hours, etc. etc. and from there you have fields separating how many hours falls into each category..

[Vaughan]

I'd like to create a formula that automatically calculates overtime for each working day.

 

As a standard week (or normal time NT) Monday to Thursday our employees work an 8.5 hour day and on Fridays they work 4 hours.

 

We pay time and a half (overtime OT) for the first 2 hours after the normal time and double time (DT) for anything beyond that.

 

So far in my head i have worked out that if

 

DayOfWeek = 2-5 (Mon - Thurs), then NT is 8.5 hours, and OT is any hours > 8.5 hrs, but less than 10.5 and DT is >10.5 hours

and if

DayOfWeek = 6 (Fri), then DT is 4 hours, and OT is > 4 but < 6 hrs and DT is > 6 hours

and finally, if

DayOfWeek = 7 or 1 (Sat or Sunday), then OT is 0-2 hrs and DT is >2hrs

 

Does this make sense?

 

Can anyone help me string the formula together? Its a bit much for my brain!

 

 

A couple of questions:

 

Can you better define what the normal time and overtime hours are: do the OT hours occur after 38 hours are worked in a week, or after 8.5 hours are worked in a day?

 

Friday is special. Is it special because it is the end of the week or because that's the day that the 38 hour week is completed. What if during the week I worked an extra hour each day?

 

If I work 9.5 hours each day Mon to Thurs (total 38 hours) and then come in for 2 hours on Friday, how much overtime do I have?

 

If I worked 7.5 hours Mon to Thurs (total 30 hrs) then worked 8 hours on Friday, do I get overtime because I worked more than 4 hours on Friday?

[Lee Smith]

Local laws determine what overtime is and how it is accrued. This question has been asked several time in the past, so a search should provide you with the information you are looking for. I just did a Goole search for overtime site: fmforums.com  and got some hits.

 

HTH

 

Lee

[comment]

See if this helps:

http://fmforums.com/forum/topic/85293-checkbox-to-incorporate-overtime-and-time-and-a-half/#entry394693

[Lee Smith]

Yep, that was one of the sites that was listed.

[emncwundy]

Thanks to Matt, Vaughan and Lee for your input and suggestions. You've raised some good questions - which has made me realise I need to think more holisitcally about how the payroll/time recording is set up.  I will do some research and probably come back with a few more questions. thanks again.