
1. Re: Calculate Work Hours By Day Type
philmodjunk May 14, 2010 2:16 PM (in response to PaulSimon)A case or IF function can handle this.
If ( day_type = "Regular" ; //calculation for regular hours ; //calculation for nonregular hours )
or
Case ( day_type = "Regular" ; //calculation for regular hours ; //calculation for nonregular hours )

2. Re: Calculate Work Hours By Day Type
comment_1 May 14, 2010 2:19 PM (in response to PaulSimon)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" ≠ "RegularI 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
PaulSimon May 19, 2010 11:24 AM (in response to PaulSimon)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 NonRegular 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
philmodjunk May 19, 2010 11:40 AM (in response to PaulSimon)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
PaulSimon May 19, 2010 12:08 PM (in response to PaulSimon)It's still not differentiating between Regular and NonRegular hours.
I'm getting Regular Hours summed as all the hours worked and the nonRegular Hours are getting no total.
This means somethings not being read right from the the day_type field, which is a dropdown 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
philmodjunk May 19, 2010 12:20 PM (in response to PaulSimon)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
PaulSimon May 19, 2010 12:35 PM (in response to PaulSimon)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 NonRegular 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
philmodjunk May 19, 2010 1:00 PM (in response to PaulSimon)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
PaulSimon May 20, 2010 8:19 AM (in response to PaulSimon)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
philmodjunk May 20, 2010 10:34 AM (in response to PaulSimon)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/tXdVoON/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.