overtime calculations

Hi again,

Here's my 'scratchboard' table for your second question.

To fit better in this space, I have shrunk the table by hiding all rows in the range 19 to 32 containing 0:00 duration values.

Column G contains the same data as on your sample table.

Column H extracts all the hours greater than 8h in a day. These are summed in H33

Column I extracts all the hours greater than 10h in a day. These are summed, and the duration in H33 subtracted in I33.

I used conditional highlighting to highlight the rows where hours exceeded 10 (orange) or 8 (yellow).

Formulas used are listed below the table.

Formulas on this table:

H2, and filled down: MAX("0h",G2−DURATION(,,8,minutes,seconds,milliseconds))

I2, and filled down: MAX("0h",G2−DURATION(,,10,minutes,seconds,milliseconds))

G33 (a Footer row): SUM(G)

I33: SUM(I)

H33: SUM(H)−I33

With these formulas showing the expected results and the values that entered the calculations, I turned to the second table, where the results could be calculated independently, referencing only the values in column G of the larger table, Table 1.

B2: SUM(Table 1::G)

Hours at 150%:

B4: SUMIF(Table 1::G,">"&DURATION(weeks,days,10))−DURATION(weeks,days,10,minutes,seconds,millis econds)×COUNTIF(Table 1::G,">"&DURATION(weeks,days,10,minutes,seconds,milliseconds))

Hours at 125%:

B3: SUMIF(Table 1::G,">"&DURATION(weeks,days,8))−DURATION(weeks,days,8,minutes,seconds,millisec onds)×COUNTIF(Table 1::G,">"&DURATION(weeks,days,8,minutes,seconds,milliseconds))−B4

The words "weeks", "days",…,"milliseconds" are not required in the Duration function, but the commas placing 10 or 8 in the third argument space (hours) are required. The three commas and three unit names following the 10 or 8 may be omitted.

Regards,

Barry

PS: Notes on all functions supported in Numbers can be found in the Function Browser. To open the browser, select any cell in a table and type an =.

B

Hi Gon Paran,

For a single shift, once you've calculated the hours, the regular pay, initial overtime and overtime+ calculations can be done as follows.

For the example, the regular pay rate is 40/hr.

OT rate for the first 2 hours is 1.25 x the regular rate.

OT for time for hours beyond 10 is 1.5 x the regular rate.

These rates are recorded in cells A2, B2 and C2 respectively of the Rates table.

The time worked is entered as a duration value in column A of the Calculations table.

All other columns in Calculations are calculated by the five formulas below, entered in the cells named, and filled down to the bottom of the named column.

Formulas:

B2: DUR2HOURS(A2)

This converts the duration in column A to a number representing the number of hours worked, simplifying the formulas that follow.

C2: MIN(8,B2)×Rates::A$2

This calculates the amount earned during the regular rate hours of the shift.

D2: IF(B2−8>0,MIN(B2−8,2),0)×Rates::A$2×Rates::B$2

This calculates the number of 'first OT rate' hours worked, and the OT pay for those hours.

If the total hours worked is less than 8, the subtraction result is less than zero, and IF returns zero.

If the total hours worked is more than 8, the result of the subtraction is a positive number, and MIN returns the result of that subtraction or 2, whichever is smaller, and IF returns the result calculated by MIN.

That result is then multiplied by the regular pay rate, and the result is multiplied by the first OT multiplier, 1.25.

E2: IF(B2−10>0,B2−10,0)×Rates::A$2×Rates::C$2

This calculates the number of 'second OT rate' hours worked, and the OT pay for those hours.

The formula is the same as the one in column D, with the threshold value raised to 10, and the rate (1.50) retrieved from C2 of the Rates table.

F2: SUM(C2:E2)

This sums the amounts in columns C, D and E to arrive at the total pay due the worker for this shift.

Regards,

Barry

ASC supports only images, such as screen shots, and links to videos hosted elsewhere. You can also copy the cells of a table, then paste that data into your post, where it will be formatted as a table, but won't contain any of the formulas—just the formatted data, including formula results, as displayed. This type of copy is useful where the person responding wants to construct a working table using the same data as you are using.

For the benefit of others searching later for answers to a question similar to yours, you should mark responses that Solve your issue with a green checkmark and responses that were Helpful with a red star. As the original poster of the question, you have one check mark and up to two stars to use for this purpose.

And, of course, "Thanks" is always appreciated.

Regards,

Barry

"So the command MAX is the one that pulls out the duration about a set "X" duration?"

MAX(value,value…) is a function that returns the MAXimum value from a list of one or more values.

H2: MAX("0h",G2−DURATION(,,8,minutes,seconds,milliseconds))

In column H of the first table, MAX is presented with two values on each row:

"0h" — zero hours (and zero minutes, zero seconds and zero milliseconds)

G2-DURATION(,,8,,,) — the duration in cell G2 minus 8 hours.

If G2 is less than 8 hours, the result is a negative duration, 0h is the MAXimum of the two value list, and MAX returns 0h.

If G2 is equal to or greater than 8 hours, the result is 0h or a positive duration value, and MAX returns the result of the subtraction.

" why subtract I33 from SUM(H) to get H33?"

Column H contains all hours over 8 worked in each shift. Up to 2h per shift is paid at 125% of the base rate. Any amount over 2h (in the same shift) is paid at 150% of the base rate.

For the figures shown in the sample table, this person has four shifts which exceeded 8 hours, shown in rows 3, 9, 10, and 17.

Rows 3, 10 and 17, though each have more than 2 hours of OT. The excess is the amount over 10 hours in that shift, calculated in column I. The total of column I, shown in I33, is paid at 150%.

If that total is not subtracted from the total of Column H, where those hours have also been included, then they will also be paid at 125% of the base rate, effectively making the pay rate for hours over 10 275% of the base rate. Nice bonus! ;-)

If you're uncomfortable with that calculation, it ca be avoided with this change in the formula in H2:

existing: H2: MAX("0h",G2−DURATION(,,8,,,))

revised: H2: MIN(MAX("0h",G2−DURATION(,,8,,,)),"2h")

This will return a duration between 0h and 2h.

SUM(H), in H33, will then give you the correct total for amount paid at 125%.

The formulas in column I are unchanged.

Regards,

Barry

"I have in this file a sheet for each month"

What tables are contained on each sheet?

Do any formulas on one sheet reference cells on a table on a different sheet?

What are the steps in "copy pasting a formula from one sheet to the other" as you are doing it. Be specific and detailed—include the type and number of clicks at each step.

Regards,

Barry

Hi Gon Paran,

"each sheen is self contained system ad they don't reference to each other."

"I simply want to duplicate the changes made on one sheet to all the others."

"duplicating the sheet is not an option as each sheet already contains data."

Working ahead, where no data yet exists, you can:

Duplicate the last sheet containing data.

Rename the duplicate, then delete all of the data (but none of the formulas) on that duplicate.

You now have one working copy of the sheet and its tables which does not "already contain data."

Before recording any data on the now data-free tables, duplicate that sheet as many times as you will need, plus 1.

Working back, you can also create duplicates of the dataless sheet, one for each month that has passed, rename them, then copy the data from the original for that month and paste it into the correct cells on the new duplicate for that month.

Regards,

Barry