filemaker script with overtime and calcuration

Posted on 2007-08-07
Last Modified: 2010-04-27
do you know how to make this script?

40 hours max per week regular work hour and if it get more, it becomes overtime.
lunch hour deduct from overtime first and if there is no overtime, deduct from regular time.
i can't figure out how to make lunch hour deduction "if script" like this case;

if overtime is 3 hour and lunch time is 4 hour, they deduct 3 hours from overtime and 1hour from regular time..

please help me.
Question by:Hiroyuki Tamura
    LVL 6

    Expert Comment

    I am going to assume that overtime is 1.5 times regular time so that if you work 41 hours in a week you actually get paid for 41.5 hours.  I am also going to assume that you need to calculate the number of hours that a person will be paid for before subtracting the lunch time. If this is the case you can do the following:

    Create 2 number fields, HoursWorked and LunchTime

    Make a Calculation field called CalcHoursWorked which results in a number:

    CalcHoursWorked =
    RegTime = If(HoursWorked>40;40;HoursWorked);
    Overtime = If(HoursWorked>40;HoursWorked-40;0);
    CalcTotalTime = regTime +Overtime*1.5

    Make 2 fields called StraightTime and OverTime which are calculations that results in numbers:

    StraightTime = If(CalcHoursWorked>40;40;CalcHoursWorked)

    Overtime = If(CalcHoursWorked>40;CalcHoursWorked-40;0)

    If you do not have to calculate the effictive hours that a person has to be paid for before subtracting the lunch, you can just subtract the lunch from the hours worked instead of going through all these steps but I don't think that is what you were asking.

    Author Comment

    by:Hiroyuki Tamura
    thank you, JoJohn2004!

    i have some problem;

    case 1
    HoursWorked: 60hours, Lunch 4 Hours

    Regular 40hours, Overtime 20hours - Lunch 4 Hours = CalcOvertime 16hours

    case 2
    HoursWorked: 44hours, Lunch 4 Hours

    Regular 40hours, Overtime 4hours - Lunch 4 Hours = CalcOvertime 0hours

    case 3
    HoursWorked: 43hours, Lunch 4 Hours

    Regular 40hours, Overtime 3hours - Lunch 4 Hours = CalcOvertime 0hours (even -1 hours)
    CalcRegular 39hours (they deduct from regular)

    it's little weird but they deduct from overtime and Lunchtime same unit...
    LVL 6

    Accepted Solution

    If they are deducting as the same unit you don't have to do anything to the data. Add up the total number of hours and subtract lunch then calculate how much time is over 40 for the overtime.

    Author Comment

    by:Hiroyuki Tamura
    thank u

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
    Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now