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
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
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,
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.
Adding the extra column worked
Thanks A million!
Mark
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
I figured it out. I used lookup instead
Time Sheet Question
