11 Replies Latest reply on May 20, 2010 10:34 AM by philmodjunk

# Calculate Work Hours By Day Type

### Title

Calculate Work Hours By Day Type

### Post

I'm starting with modifying the Starter Solution Time Card.
I've added more entries to the day types and added a second set of In and Out times to each day.

No problem getting the calculations for work hours to function correctly for this. The only stumbling block I've encountered so far is I want to generate hours worked using two sets of conditions.

Condition 1 calculates work hours based on "day_type" = "Regular"
Condition 2 calculates work hours based on "day_type" ≠ "Regular"

A bonus is calculating overtime as anything over 40 "Regular" hours, which excludes any other type of "day_type" since those don't contribute to overtime pay.

I haven't been able to figure out how to do that, yet.
Any help or guidance would be appreciated.

Thanks.

• ###### 1. Re: Calculate Work Hours By Day Type

A case or IF function can handle this.

If ( day_type = "Regular" ; //calculation for regular hours ; //calculation for non-regular hours )

or

Case ( day_type = "Regular" ; //calculation for regular hours ; //calculation for non-regular hours )

• ###### 2. Re: Calculate Work Hours By Day Type

I am not sure what you mean by this:

Paul Simon wrote:
I want to generate hours worked using two sets of conditions.

Condition 1 calculates work hours based on "day_type" = "Regular"
Condition 2 calculates work hours based on "day_type" ≠ "Regular

I see only one condition here, and no clue what should be calculated if it's true and what otherwise.

Paul Simon wrote:
A bonus is calculating overtime as anything over 40 "Regular" hours

Try something like =

Max ( 0 ; TotalHours - 40 )

• ###### 3. Re: Calculate Work Hours By Day Type

Not sure.

I calculate the hours worked in a day regardless of day_type.

I want sum up the daily hours worked as Regular Hours and Non-Regular Hours (in different calculation fields) by summing up the work hours based on the day_type.

Does that make sense?

• ###### 4. Re: Calculate Work Hours By Day Type

Just a slight variation on the previously posted If or case functions will do this:

If ( day_type = "Regular" ; //calculation for regular hours ; "" )  (You can also leave out ; "" and get the same results)

If ( day_type ≠ "Regular" ; //calculation for non regular hours ; "" )

Put each in a different field and use Comment's calc for over time hours.

• ###### 5. Re: Calculate Work Hours By Day Type

It's still not differentiating between Regular and Non-Regular hours.

I'm getting Regular Hours summed as all the hours worked and the non-Regular Hours are getting no total.

This means somethings not being read right from the the day_type field, which is a drop-down value list:

Regular

-

Holiday

Vacation

Sick Day

Bereavement

Miscellaneous

Training

-

The calculation for Hours_Worked_This_Week_Reg is:

If ( Day_Type = "Regular" ; Round(Sum( Work Hours ); 2))

The calculation for Work_Hours is:

Round(

Hour( Time_End_1 - Time_Start_1 ) + Minute( Time_End_1 - Time_Start_1 ) / 60;

2)

+

Round(

Hour( Time_End_2 - Time_Start_2 ) + Minute( Time_End_2 - Time_Start_2 ) / 60;

2)

- Hours_Lunch

• ###### 6. Re: Calculate Work Hours By Day Type

What you've posted should work. Perhaps you could upload the file to a file sharing site and post the link here. Then we could take a look at the file.

• ###### 7. Re: Calculate Work Hours By Day Type

Oh, just discovered this gem.

If my first day_type of the week is regular I get the sum as I said above.

If I change it to anything else the total changes to Non-Regular Hours.

I guess this is since it's a repeating field it doesn't matter what I put there in subsequent entries, only the first one gets read.

Am I right in thinking repeating fields for the week are not the way to go and I should be using a portal instead?

Hope not because I have no idea where to start.

I suppose if I have to it only helps me learn and be less of a novice in the long run.

Thanks again.

• ###### 9. Re: Calculate Work Hours By Day Type

That these are repeating fields is the missing detail. We can fix this, but a much better design would be to toss out the repeating fields and replace them with a portal to a related table where each field repetition row has been replaced by a separate related record. Look up "Portals" in filemaker help to learn more.

To fix the actual calculation:

Your calculation must also be defined as a repeating field. Since you have 7 repetitions in your layout, you must also have 7 repetitions specified for your calculation. Use the box in the lower left corner of specify calculation to set it up with 7 repetitions.

• ###### 10. Re: Calculate Work Hours By Day Type

I did change the calculation to seven repetitions, but doesn't work.

For now I'm doing as you suggested and setting it up as a portal.

Can I use self related fields for this from the current table?

• ###### 11. Re: Calculate Work Hours By Day Type
I did change the calculation to seven repetitions, but doesn't work.

If you're interested, compare your design to this test file I used to make sure my information was correct: http://www.4shared.com/file/tX-dVoON/RepeatingFIeldCalc.html

For now I'm doing as you suggested and setting it up as a portal.

Can I use self related fields for this from the current table?

I can't really say from here; I don't know enough about your table structure.