Limabeancarson
Level 1 (8 points)
iWork

Q: Time Sheet Question

Hi All,

I am trying to build a spreadsheet to track hours.

I am trying to figure out if there is a way to do the following

 

Cell A:1 is the hours work Lets say 50

Cell B:1 Is the overtime hours in this case 10

I would like to know if there is a formula that once A:1 is greater than 40

B:1 will put that number in it. In this case the value would be 10

Then I could put in cell A:1  sum(A:1-B:1) and  that in theory would never let A:1 go over 40

 

 

 

Thanks

iMac, iOS 9.0.2

Posted on Oct 8, 2016 11:26 AM

by Wayne Contello,Solvedanswer
Wayne Contello Wayne Contello
Level 6 (19,416 points)
iWork
A:

The error is telling you the problem.  You cannot put a formula in a cell that refers to the cell the formula is in.  This is called a circular reference.

 

imagine if I select cell A1, then put the formula

=A1

 

What would the value of cell A1 be?

 

 

notice in the example, I provided a cell where you input ALL of the hours and there were two additional columns the operated the hours into regular hours and overtime hours.

 

I think you will need one extra column to hold the regular hours.

Posted on Oct 9, 2016 11:35 AM

Close
Limabeancarson

Q: Time Sheet Question

  • All replies
  • Helpful answers

  • by Wayne Contello,Helpful

    Wayne Contello Wayne Contello Oct 9, 2016 6:21 AM in response to Limabeancarson
    Level 6 (19,416 points)
    iWork
    Oct 9, 2016 6:21 AM in response to Limabeancarson

    Screen Shot 2016-10-08 at 10.45.31 PM.png

     

    This is not the approach you asked for but it one that will work.  What you asked for is not really possible in Numbers.

    enter you actual hours worked in column B ("Hours Worked")

    C2=IF(COUNTA(B2)>0, MIN(VALUE(B2), 40), "")

     

    this is shorthand for… select cell C2, then type (or copy and paste from here) the formula:

    =IF(COUNTA(B2)>0, MIN(VALUE(B2), 40), "")

     

    D2=IF(COUNTA(B2)>0, B2−C2, "")

    select cells C2 and D2, copy

    select cells C2 thru the end of column D, paste

     

    now enter the hours in column B

  • by Limabeancarson,

    Limabeancarson Limabeancarson Oct 9, 2016 6:21 AM in response to Limabeancarson
    Level 1 (8 points)
    iWork
    Oct 9, 2016 6:21 AM in response to Limabeancarson

    Hi Wayne,

    Thanks  for the tip. I tried it on a blank Sheet and it worked.

    Then when I put it into my spreadsheet it threw an error.

    My time sheet is a little more complicated I have attached a couple of screenshots.

    So here is the long version

    So, Some employees are piece and some are hourly. they also can work on multiple jobs in any given week

    So in the TS table the employee is selected and the job he worked on and the hours or amount of work he completed.

    the employee can be on multiple cells because of different jobs he worked on.

    In the "employee hours" table his name will only show in one cell in this case "A2" Cell "C2" totals the hours based on the amount he earned divided by his pay.

    I am trying to get the "Overtime Cell" to automatically show the over 40 house amount so I can track OT time VS. regular time on each project.

    I can manually enter it and it works.

    I created a new column in the Employee hours table and put in the formulas you have and I get

     

    "This Formula can't reference own cell or depend on another formula that references this cell"

     

    So i'm not sure if numbers is capable of doing this or not.

     

    Thanks,

    Screenshot 2016-10-09 05.59.44.png

    Screenshot 2016-10-09 06.06.44.png

  • by Wayne Contello,Solvedanswer

    Wayne Contello Wayne Contello Oct 9, 2016 11:35 AM in response to Limabeancarson
    Level 6 (19,416 points)
    iWork
    Oct 9, 2016 11:35 AM in response to Limabeancarson

    The error is telling you the problem.  You cannot put a formula in a cell that refers to the cell the formula is in.  This is called a circular reference.

     

    imagine if I select cell A1, then put the formula

    =A1

     

    What would the value of cell A1 be?

     

     

    notice in the example, I provided a cell where you input ALL of the hours and there were two additional columns the operated the hours into regular hours and overtime hours.

     

    I think you will need one extra column to hold the regular hours.

  • by Limabeancarson,

    Limabeancarson Limabeancarson Oct 9, 2016 11:36 AM in response to Wayne Contello
    Level 1 (8 points)
    iWork
    Oct 9, 2016 11:36 AM in response to Wayne Contello

    Adding the extra column worked

     

    Thanks A million!

     

     

    Mark

  • by Limabeancarson,

    Limabeancarson Limabeancarson Oct 14, 2016 8:51 PM in response to Wayne Contello
    Level 1 (8 points)
    iWork
    Oct 14, 2016 8:51 PM in response to Wayne Contello

    Hi Wayne,

    So I am having an issue with a column running the numbers out correctly

    So In table Employee hours take Hector, He should have 40 regular and 3 OT total of 560 on reg time but it is grabbing the wrong rate and therefor not giving the right dollar amount it is returning 480.00 which is $12.00 hr and therefore also giving a incorrect OT value .

    Is there a way to make it grab the correct employee hourly pay?

    I have a separate table that has each employees hourly wage.

     

    Thanks,

     

    Mark

     

    Screenshot 2016-10-14 20.41.07.png

  • by Limabeancarson,

    Limabeancarson Limabeancarson Oct 15, 2016 9:15 PM in response to Limabeancarson
    Level 1 (8 points)
    iWork
    Oct 15, 2016 9:15 PM in response to Limabeancarson

    I figured it out. I used lookup instead