Calculations if multiple conditions are met

  • Hi,

    Thank you in advance for viewing my post.

    I need to calculate the amount of money a person will make depending on the following criteria.

    • Any time they worked on Friday, Saturday, and Sunday will be calculated using Overtime rates
    • Any time they work between Mon-Thursday that is 40 hours or less, will be calculated using Straight Time rates. Any time they worked between Mon-Thursday that is greater than 40 hours will be calculated using Overtime rates.

    What is making this a bit of a challenge is that some of the shifts are 13.5, or 12.5 shifts. That means for example, on Wednesday, a person will have worked 40.5 hours total. So for that day, the code needs to calculate 13 hours of Straight time, and 0.5 hours of Overtime. Then on Thursday, the code needs to calculate the 13.5 hours as OT. The issue becomes more complicated because we might have more than one or each craft member working, so now the code needs to account for the situation of multiple personnel.

    The uploaded sheet shows how one person working 13.5 hours from Monday through Wednesday and on Wednesday, the formula calculated 13 hours of ST and .5 hours of OT. Thursday shows their wage calculated using Overtime.

    Right now, I have to do all of these by hand and at times there are over 60 people scheduled to work per day / per shift. So any help would be great.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!