Time card regular and overtime

"Sorry but I can’t seem to get that formula to work. I keep getting errors. "

• What is the formula (as written in your table) giving you errors?
• What are the error messages?

Using Ian's formula for column F, I would recommend these two for columns G and H:

G2: =MIN(Regular hours per day::$A$1,F2)

H2: =MAX(0,F2-Regular hours per day::$A$1)

The image is of the last three columns of Ian's sample table. Column F values have been entered manually. The two formulas shown above are entered in G2 and H2 respectively, then filled down to Row 6.

Row 7 of the table is a Footer Row.

Row contains the formula below, entered in F7, then filled right to H7.

F7: SUM(F)

Regards,

Barry

Hi verenis80,

Jumping in here.

Cells B2:E8 are formatted as Date & Time with Date set to None.

Cells F2:H9 are formatted as Duration hr min (rather than the ambiguous 7:58 which could be a time of day).

The single-cell table "Regular hours per day" is formatted as Duration.

Formula in F2 =E2−B2−(D2−C2)

I don't know why that formula does not replace "(" and ")" with the usual crescent moons. If I delete "(" and ")" the formula throws an error. Perhaps another user can explain.

Formula in G2 =IF(F2≤Regular hours per day::$A$1,F2,Regular hours per day::$A$1)

Formula in H2 =F2−G2

Fill those formulas down.

Formula in Footer cell F9 =SUM(F)

Fill right.

Please call back with questions.

Regards,

Ian.

Hi verenis80,

The formulas will work in macOS and iOS

Sorry, I should have posted this screen shot.

"Regular hours per day" is a single-cell table formatted as Duration.

Formula in G2 =IF(F2≤Regular hours per day::$A$1,F2,Regular hours per day::$A$1)

Enter <= and Numbers will change it to ≤

Please reply if you are still having trouble.

Regards,

Ian.

Hi verenis80,

Ian's solution, and my revisions both placed the "regular hours" on a separate single cell table, which Ian named "Regular hours per day" (with a capital R).

The formulas supplied by Ian, and the formulas provided by me both got that fixed value from that table, which is why the cell reference to the cell containing that value includes the table's name,

You chose to include the 'regular hours' value in a cell on the same table as the formulas using that value,

Formulas addressing cells on their own table do not need to include the table name as part of the address of the cell(s) referenced.

Notice the differences between the display of the Table name," regular hours per day" in the formulas from your screenshot and from mine:

Yours:

Mine:

The top one shows the 'table name' in plain type because your document does not include a tale with that name.

(Even if it did have a table named "Regular hours per day", I suspect your displayed formula would show the same format due ti the lower case r not matching the actual text of the table name).

Delete this text, and the two colons, from your formula, and report any changes.

IF there are still error triangles, please supply the formula in the cell containing the error AND the error message for that cell.

Regards,

Barry

Hi Ian,

The 'crescent moon' and it's partner, the scooped out right end of a function name are always the opening and closing parentheses of a function. The 'plain' parentheses in this formula (which contains no functions) are signals to direct the Order of Operations in the expression to ensure that the D2-C2 subtraction is done before subtracting the result from the result of E2-B2.

10-2-5-3 = 0

10-2-(5-3)=6

A handy mnemonic for the basic Order of Operations is this one that I've used in elementary school classrooms:

Bless My Dear Aunt Sally!

meaning : Brackets, Multiplication and Division, Addition and Subtraction.

Details: within each level of brackets (parentheses), do all the multiplication and division, then do all the addition and subtraction.

Regards,

Barry

"the formula" depends on what constitutes "overtime hours" in your location.

Are they time in excess of a specific daily limit? If so, what is that limit?

Are they time in excess of a specifis weekly limit? If so, what is that limit?

Essentially, your formula is going to be the same as the one you'd use to do the calculations using pencil and paper.

Regards,

Barry

Hi Barry,

Thanks for the explanation.

Barry wrote:

Bless My Dear Aunt Sally!

meaning : Brackets, Multiplication and Division, Addition and Subtraction.

Or, (nothing at all to do with this discussion!)

SOHCAHTOA taught in the UK as a mnemonic for trigonometry 😈.

Regards,

Ian.

I remember Please Excuse My Dear Aunt Sally. Parentheses, exponents, multiplication/division, addition/subtraction.

And don't do multiplication before division, addition before subtraction, but instead work from left to right.

And yes, the fearful SohCahToa and Tommy, Oscar, etc.

SG

verenis80 wrote:

THANK YOU SO MUCH! That worked!

Which post is the "that" that worked? You might consider marking it.

SG