H
hendrjon
Column D has a formula in it =IF(B15>=34,"70",IF(B15<34,70-C15))
it reports a 70 if someone takes 34 hours off in a row, or subtracts 70 from whats in colum C.
Column C has a formula in it=SUM(A9:A15) but it needs to report a "0" or the sum of the hours of the last 7 days worked in column A.
Column E has a formula in it =SUM(A2:A11) reporting the hours of the last 8 days worked in column A.
Cell A,B, has no formulas.
A is numbers put in for hours worked each day.
B is used to make Column D report a 70 or a calculation. Don't really want a column B involved but seems only way so far to get the 70 to report in column D.
The idea is to have Column C check Column D to see if a 70 is showing, if so then put a "0" in Column C, or Show a total of the hours in the last 7 rows on Column A, if column D is showing the calculation it is supposed to.
If i put a 35 in Column B then Column D shows a 70, If i put a 22 in Column B then Column D shows a total of what's in Column C -70. And that works.
The 35 and 22 mean nothing other than (greater than or equel to 35) or (less than 35).
I need Column C to show "0" if Column D shows a 70, also if Column D is showing a number other than 70 then Column C needs to do the calc of the hours worked for the last 7 rows in Column A and show that total.
my problem is the formula i am trying =IF(D9=70,"0"),IF(D9<70, SUM(A3:A9)), reports a #VALUE! in C when I put a 35 in Column B, when I PUT 22 in column B it reports a #VALUE! in both cells C,D upsetting both Cells, instead of a 0 or a running total just in C.
I am not sure what it needs to do. Formulas need to see other formulas as numbers? Or how to get them from canceling each other out because they both a reporting a result from each other.
heres the layout below:
A B C D E
Hours Hours Not Last 7 days Hours aval Last 8 days
Worked worked hours worked hours worked
10
7
8
12
5.5
0.5
0 22 #VALUE! #VALUE! this row has the new formula i have been trying =IF(D9=70,"0"),IF(D9<70, SUM(A3:A9)) this dosen't work.
0 45 43 70 55
12 55 15 49
11 56 14 46.5
5.5 34.5 35.5 37.5
3 22 32 38 34
2 35 33.5 70 33.5
The real need for this sheet is to calculate the last 7 days of hours worked and show the totals and, the total hours subtracted from 70 is hours left for the next day.
They can't work over 70 hours in a week 7 days.
I need hours worked each day.
I need total hours worked last 7 days to be calculated and hours avalible calculated. (70 minus hours worked last 7 days.)
If they take 34 or mores hours off in a row then it goes back to 70 and then next day it starts over.
So it should look like this:
Hours Hours Not Last 7 days Hours aval Last 8 days
Worked worked hours worked hours worked
10
7
8
12 if I can do this w/o using column B would
5.5 be better.
0.5
0 22 43 27
0 35 0 70 0
12 35 0 70 0
11 22 56 14 46.5
5.5 22 34.5 35.5 37.5
3 22 32 38 34
2 35 0 70 0
5.5 22 39 31 39
Can someone give me a hand on this? it boggleing me.
Thank you tons
If i need to send the sheet via email let me know.
If you need to send email please use
(e-mail address removed)
it reports a 70 if someone takes 34 hours off in a row, or subtracts 70 from whats in colum C.
Column C has a formula in it=SUM(A9:A15) but it needs to report a "0" or the sum of the hours of the last 7 days worked in column A.
Column E has a formula in it =SUM(A2:A11) reporting the hours of the last 8 days worked in column A.
Cell A,B, has no formulas.
A is numbers put in for hours worked each day.
B is used to make Column D report a 70 or a calculation. Don't really want a column B involved but seems only way so far to get the 70 to report in column D.
The idea is to have Column C check Column D to see if a 70 is showing, if so then put a "0" in Column C, or Show a total of the hours in the last 7 rows on Column A, if column D is showing the calculation it is supposed to.
If i put a 35 in Column B then Column D shows a 70, If i put a 22 in Column B then Column D shows a total of what's in Column C -70. And that works.
The 35 and 22 mean nothing other than (greater than or equel to 35) or (less than 35).
I need Column C to show "0" if Column D shows a 70, also if Column D is showing a number other than 70 then Column C needs to do the calc of the hours worked for the last 7 rows in Column A and show that total.
my problem is the formula i am trying =IF(D9=70,"0"),IF(D9<70, SUM(A3:A9)), reports a #VALUE! in C when I put a 35 in Column B, when I PUT 22 in column B it reports a #VALUE! in both cells C,D upsetting both Cells, instead of a 0 or a running total just in C.
I am not sure what it needs to do. Formulas need to see other formulas as numbers? Or how to get them from canceling each other out because they both a reporting a result from each other.
heres the layout below:
A B C D E
Hours Hours Not Last 7 days Hours aval Last 8 days
Worked worked hours worked hours worked
10
7
8
12
5.5
0.5
0 22 #VALUE! #VALUE! this row has the new formula i have been trying =IF(D9=70,"0"),IF(D9<70, SUM(A3:A9)) this dosen't work.
0 45 43 70 55
12 55 15 49
11 56 14 46.5
5.5 34.5 35.5 37.5
3 22 32 38 34
2 35 33.5 70 33.5
The real need for this sheet is to calculate the last 7 days of hours worked and show the totals and, the total hours subtracted from 70 is hours left for the next day.
They can't work over 70 hours in a week 7 days.
I need hours worked each day.
I need total hours worked last 7 days to be calculated and hours avalible calculated. (70 minus hours worked last 7 days.)
If they take 34 or mores hours off in a row then it goes back to 70 and then next day it starts over.
So it should look like this:
Hours Hours Not Last 7 days Hours aval Last 8 days
Worked worked hours worked hours worked
10
7
8
12 if I can do this w/o using column B would
5.5 be better.
0.5
0 22 43 27
0 35 0 70 0
12 35 0 70 0
11 22 56 14 46.5
5.5 22 34.5 35.5 37.5
3 22 32 38 34
2 35 0 70 0
5.5 22 39 31 39
Can someone give me a hand on this? it boggleing me.
Thank you tons
If i need to send the sheet via email let me know.
If you need to send email please use
(e-mail address removed)