Time Cards Formula Question

Question

Level 1

0 points

Time Cards Formula Question

Hi all,

I can do the most basic of formulas and cell references, but there is one formula I need to figure out that would make my life waaaay simpler when doing time cards for my employees. In the attached screenshot, you can see that the selected cell in the "total hours" column is a formula that determines the total length of time worked in tenths of an hour minus any lunch breaks. The trouble is, our contract is such that the 1st 8 hrs worked is straight pay, hours 8-12 are 1.5x pay, and 12+ is 2x pay. I would like to tell the "1.5x" column somehow that IF the "total hours" number is GREATER than 12, then for that value to automatically be 4. And then for the "2x" column to determine the difference between "total hours" and 12. Currently, I have to enter those values manually in the screenshot. I use a 1.5x formula of "X-8" but obviously that only works if the total hours is 12 or less. Thanks in advance for any help!

Posted on Jun 2, 2022 1:16 PM

Reply

Answer 1

Top-ranking reply

Ralf-F

Level 4

2,717 points

Jun 2, 2022 2:41 PM in response to Ironlungs

Here my proposal

Formula for cell D2: IF(A2>12,A2−12,0)

Formula for cell C2: IF(A2>8,A2−(8+D2),0)

Formula for cell B2: A2−(C2+D2)

If you don't like that the 0.0 is shown you can create a custom format for these cells.

Just select all cells in these columns

This is how you can create a custom format

Create a custom cell format in Numbers on Mac – Apple Support (UK)

This is how it should look like, then 0 will be blank

Based on your region the , or the ; will be used to separate the different sections of a formula.

Hope this will help you with your time cards

Ralf

Reply

Answer 2

Barry

Level 9

59,951 points

Jun 2, 2022 3:27 PM in response to Ironlungs

Here is an example containing formulas for the 1x (Q), 1.5x (R) and 2x (S) columns in your table. The formulas are shown below the table. Each is entered in row 2 of its column, then filled down to as many rows below as needed.

Testing table (same formulas)

Note that I have included the 0.0 results in the table, but have used conditional highlighting rule to grey the numbers where they are 0.0. If you want these cells to appear blank, use a custom highlight setting that colours the characters white for cells with zero values.

Regards,

Barry

Reply