Help with Overtime Calcs

[William Block]

I have put together a simple timesheet app for temp workers. My original calculation for overtime worked well: I used Min(hours;8) for regular hours and Max(hours-8;0) for the overtime. However, I've run into a snag. The company I worked for changed the way they pay overtime. Now, overtime is based on a 40 hr week, not an 8 hour day. So, if an employee works 10 hours a day there is no overtime. When the employee reaches 40+ hours in a week-overtime is applied. I have a summary field for hours-that I;ve tried to incorporate into the solution--but it just doesn't work. I'm having a very difficult time figuring out how to calc the regular hours up to 40--then overtime hours over 40. The time is entered as a daily value. any ideas would be greatly appreciated! Thanks in advance!

Edited February 2, 2006 by Guest

[comment]

Do I understand this correctly - you're asking how to aggregate the daily sheets into a week?

[Lee Smith]

I'll play devil's advocate here.

The schedule week is 10 hrs a day and 4 days a week Sun - Sat.

What happens if:

If an employee works 8 hours on Mon, 12 hours on Tue, then 10 hours on Wed and 10 on Thu.

what about 8 hours a day 4 days, and 12 hours on the 5th. Is that 2 hours or 4 hours.

in other words, you can have a lot of variation of the schedule that could make things harder to calculate for.

Lee

[color:red]btw, what did your question have to do with the [color:blue]Making a Living Topic?

I moved your question to this topic, in the future, please read the descriptions of the Topics before posting

Thank you,

Lee

[Ender]

I think William is looking at the hours total for the week. If the total is greater than 40, then the first 40 is regular, and the remainder is overtime. The overtime logic in my time card solution works the same way.

In my solution, those totals are calculated in a Payperiod table. It's the main interface for my time card database, keeps all the related Hours tied together, and calculates those weekly totals.

Hmm, this is kind of about "Making a Living," don't you think? It just depends on how you look at it. :

Edited February 2, 2006 by Guest

[Lee Smith]

Perhaps you are correct, I've ran into a few variations with this problem, and you should know what to expect before you go too far with setting up the calculation.

With your logic, all questions by us should be in the "Making a Living" topic. :giggle:

[William Block]

Sorry about posting in the wrong place.. You're right.. I'm looking for the hours over 40 hours/week. It doesn't matter how many hours someone works each day. So 12,10,10,10 = 2 hrs. overtime; as does 8,8,10,8,6. Any hints/ideas on the best way to do this?

Thanks again

[comment]

What should happen if someone's working day crosses over to the following week?

[William Block]

All weeks are Sunday through Sunday--no hours carry over into the next week

[comment]

I mean if someone starts on Saturday and finishes on Sunday.

[William Block]

The week runs from Sunday to Sunday. There is no overlap. No overlap from Sunday to Monday. All workers hours are calculated by the day and then totalled for the week to calcualte the total pay and any overtime. Easy math--but difficult for me to figure out in Filemaker.

[comment]

I am afraid you keep missing my point. If I work from Saturday evening until Sunday morning - do I fill one time sheet, or two?

[William Block]

It doesn't happen

[comment]

OK, then what exactly is the problem? Do you want this as a report or a calculation? If it's a report, find the sheets for this week, summarize by worker, and use:

Case (

GetSummary ( sHours ; WorkerID ) > Time ( 40 ; 0 ; 0 ) ;

GetSummary ( sHours ; WorkerID ) - Time ( 40 ; 0 ; 0 )

)

to calculate the overtime - sHours being a summary field (total of hours worked).

If you want it as a calculation, you will need to add a field to calculate a common value for all sheets of the same week. Then you can use that for a relationship.

[William Block]

Thanks very much for the suggestion! I am trying to work it out right now.. one quick question.. What does the "Time" field represent?

Thanks again

[William Block]

A little more info about my fields, etc.. I use and employee number from and employee table to identify the employee; hours-to enter the daily hours; summary of hours to total hours for a selected period. The pay rates are simple calcs based on the number of hours multiplied by the rate. For example: Employee 1 works a total of 44 hours during the pay period. The report need to show the 40 hours @ the regular pay rate--and 4 hours at the overtime rate.

Thanks again for you help!

[William Block]

Oops..sorry for being so dense about the "Time" function.. I'm using it now.. but for some reason I am getting "0" returned??

[William Block]

Ok..I changed the "Time" function, actually removed it and just used the following:

Case (

GetSummary ( Summary of Hours ; Employee Number ) > 40 ;

GetSummary ( Summary of Hours ; Employee Number ) -40)

And it works..sort of. I get the correct nummber of OT hours for a particular worker--and I can calculate the total OT pay due that employee. However, when I get to the bottom of the report--and want a Grand Total of all OT..I get an incorrect total $408.33, instead of $102.08 (which is 4x the correct amount and corresponds to the 4 daily entries for that particular employeee). Any idea how to fix this? I don't have a separate table for "hours".. would that help?

Thanks again!

[comment]

Mhmmm... I don't know a way to re-summarize a calculation based on a summary. If you need this, I'd suggest you go to Plan B, and do the calculation and the reporting from the Employees table.

[William Block]

Thanks! I'll give a try.

[comment]

Here's a little demo.

OvertimeSummary.fp7.zip

[William Block]

Ok, I've gone with plan B-and it works!! Except for one important detail--I need to show the dates and hours worked on those dates on the report. I am using the Date field from the Hours Entry Table and the Hours from the Hours Entry Table--but it only shows One date--apparently the first date entered. Is there any way to display all of the dates worked and hours worked during the period? Would I have to use a portal?

Thanks again for your help!

[William Block]

Thank you very much for the demo file. I really appreciate it and helps explain a lot. I have most of my solution working now--again, with one exception. I need to show the days/hours worked along with the summary hours and OT hours on the report. I have it all working--but I am so far I can't figure out how to search for and display only a certain set of records--or range of dates. Everytime I search, the portal I've set up to display the days/hours details, shows all of the days/hours for the employee. Any ideas? And I'd be glad to send you my file if it will help.

Thanks again!

[comment]

I need to show the days/hours worked along with the summary hours

OK, that changes things - considerably. To report the individual timesheets, the report must be done from the sheets table.

So we are back to the first problem: how to sub-summarize the sheets by worker/week, compute the OT from the sub-summary, then re-summarize the results?

I think it's time for Plan C. If anyone has a more elegant solution for this, that does not require the dirty trick I've used (and does not duplicate data) - I would welcome seeing it.

OvertimeSummary8.fp7.zip