I am trying to create a spreadsheet to calculate overtime amounts owing based on a few factors:. The parameters are as follows:
- there are two columns one for job A (Classes taught) and one for job B (Customer Service Hours);
- the number of rows in the spread sheet is 365 (one for each day of the year);
- The rate of pay for Job A is a fixed amount per class taught;
- The rate of pay for Customer service hours worked will incur overtime charges as follows:
- if the individual has "worked" more than five days (i.e. taught a class OR worked customer service or both) in a row then on the 6th
day Customer Service hours are paid at time and a half. (1.5 times customer service rate)
- On the 7th and subsequent days, customer service hours are paid at double time (2 times customer service rate) as long
as the person has either taught a class or worked customer service hours.
- The overtime payment cycle is only broken when there is a day where the person has NOT taught a class and NOT worked
any customer service hours.
The cycle can repeat - i.e. works six days in a row, the 6th day customer service hours are paid at time and a half and on the seventh day customer service hours will be paid at double time. On 8th day no class and no customer service hours. Starting on 9th day the cycle would reset; The cycle would "reset" anytime there is a day when no classes are taught and no customer service hours are worked.
I have attached a preliminary spreadsheet example.
I am reasonably fluent with Excel in a basic way, but this one is some what beyond me... any help would be greatly appreciated.
Best regards, Dave Melnyk