Formula to show a zero when options is not there.

[Robert Wyatt]

I'm trying to fix up a time sheet to show hours worked in corresponding cells I have included a XL2BB copy of what I'm working on and how I would like for it to work. can you tell me how to make it show zero when options show Off. once you look at the sample, I'm sure yo

Book3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1										Overtime Options
2											FALSE
3										After:	8	Hrs
4
5										TRUE	TRUE
6										After:	40	Hrs
7
8
9
10
11
12
13
14
15
16
17	Weekday	Options	Shift Starts	Lunch Starts	Lunch ends	Shift Ends	Total Hrs	Regular Hrs	Overtime Hrs	Sick	Holiday	vacation
18
19	Mon 12/30	Regular	8:00 AM	11:30 AM	12:00 PM	4:30 PM	8.00	8.00	-	FALSE	FALSE	FALSE
20	Tue 12/31	Regular	8:00 AM	11:30 AM	12:00 PM	4:30 PM	8.00	8.00	-	FALSE	FALSE	FALSE
21	Wed 01/01	Holiday	8:00 AM	11:30 AM	12:00 PM	4:30 PM	FALSE	-	-	FALSE	8.00	FALSE
22	Thu 01/02	Regular	8:00 AM	11:30 AM	12:00 PM	4:30 PM	8.00	8.00	-	FALSE	FALSE	FALSE
23	Fri 01/03	Regular	8:00 AM	11:30 AM	12:00 PM	4:30 PM	8.00	8.00	-	FALSE	FALSE	FALSE
24	Sat 01/04	Off					FALSE	-	-	FALSE	FALSE	FALSE
25	Sun 01/05	Off					FALSE	-	-	FALSE	FALSE	FALSE
26							Total Hrs:	32.00	-	-	8.00	-
Sheet1

Cell Formulas
Range	Formula
H19:H25	H19	=G19-I19
I19:I25	I19	=ROUND(MAX(IF($K$5,MAX(0,SUM(H$18:H18)+G19-$K$6),0),IF($K$2,IF(G19>$K$3,G19-$K$3,0),0)),2)
J19:J25	J19	=IF(B19="Sick",MROUND((MOD(F19-C19,1)-MOD(E19-D19,1))*24,1/4))
K19:K25	K19	=IF(B19="Holiday",MROUND((MOD(F20-C20,1)-MOD(E20-D20,1))*24,1/4))
L19:L25	L19	=IF(B20="Vacation",MROUND((MOD(F20-C20,1)-MOD(E20-D20,1))*24,1/4))
H26:L26	H26	=SUM(H19:H25)
G19:G25	G19	=IF(B19="REGULAR",MROUND((MOD(F19-C19,1)-MOD(E19-D19,1))*24,1/4))

u'll see what I'm doing.

 

[Trebor76]

Hi Robert,

Just add another IF statement where required so the formula in G19 would become this:

=IF(B19="Off",0,IF(B19="REGULAR",MROUND((MOD(F19-C19,1)-MOD(E19-D19,1))*24,1/4)))

I'd also check the formulas for Holidays and Vacation (columns K and L respectively) as I think they are out by one row.

Regards,

Robert

 

[Robert Wyatt]

I tried it with the formula showed above, but it still adds the hours to both regular and sick. What is it I'm doing wrong? If I have regular showing in options, I need it to add the hours to regular, If I have sick in options, I need it to add or hour to regular and 8 hours to Sick. Same for Holiday and Vacation if options show it to be Vacation or Holiday. I use to have a time sheet made up like that before, but the life of me I can't find it anywhere.

 

[Trebor76]

What is it I'm doing wrong?

Not sure Besides Col. G you need to add the extra IF statement to the formulas in columns J, K and L. Did you do that? If so, if you change B19 to "Off" you should only see zeros. Also make sure the formulas in columns K and L are referenced correctly (as I mention above) and that the calculation method for the sheet is set to automatic.

Beyond that I'm not sure I'm afraid.

 

[Robert Wyatt]

Though it didn't help I do thank you for trying. I will just give up and move on to something else. Sense it didn't work don't know if I should click the check mark or not if so let me know and I'll come back to take care of it. Thanks again for trying to help.

 

[Trebor76]

Can you please update all the formulas and re-post sheet?

 

[Robert Wyatt]

As you can see it is placing time in both sick and regular hours place when it is marked regular hours and show false when marked off

Book3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
17	Weekday	Options	Shift Starts	Lunch Starts	Lunch Ends	Shift Ends	Total Hrs	Regular Hrs	Overtime Hrs	Sick	Holiday	vacation
18
19		Sick	8:00 AM			4:00 PM	FALSE	-	-	8.00	FALSE	FALSE
20		Holiday	8:00 AM			4:00 PM	FALSE	-	-	FALSE	8.00	FALSE
21		Vacation	8:00 AM			4:00 PM	FALSE	-	-	FALSE	FALSE	8.00
22		Regular	8:00 AM			4:00 PM	8.00	8.00	-	8.00	FALSE	FALSE
23		OFF					-	-	-	FALSE	-	-
24		OFF					-	-	-	FALSE	-	-
25		OFF					-	-	-	FALSE	-	-
26							Total Hrs:	8.00	-	16.00	8.00	8.00
27							Pay Rate / Hourly:	$14.99	$22.49	$14.99	$14.99	$14.99
28							Pay:	$119.92	$0.00	$239.84	$119.92	$119.92
29
30										Grand Total Gross:	$599.60
Time Sheet

Cell Formulas
Range	Formula
I19:I25	I19	=ROUND(MAX(IF('Data Sheet'!$B$5,MAX(0,SUM(H$18:H18)+G19-'Data Sheet'!$B$6),0),IF('Data Sheet'!$B$2,IF(G19>'Data Sheet'!$B$3,G19-'Data Sheet'!$B$3,0),0)),2)
J19	J19	=IF(B19="Off",0,IF(B19="Sick",MROUND((MOD(F19-C19,1)-MOD(E19-D19,1))*24,1/4)))
K19:K25	K19	=IF(B19="Off",0,IF(B19="Holiday",MROUND((MOD(F19-C19,1)-MOD(E19-D19,1))*24,1/4)))
L19:L25	L19	=IF(B19="Off",0,IF(B19="Vacation",MROUND((MOD(F19-C19,1)-MOD(E19-D19,1))*24,1/4)))
J20:J25	J20	=IF(B20="Sick",0,IF(B20="REGULAR",MROUND((MOD(F20-C20,1)-MOD(E20-D20,1))*24,1/4)))
H26:L26	I26	=SUM(I19:I25)
I27	I27	=ROUND(1.5*H27,2)
J27	J27	=H27
K27	K27	=H27
L27	L27	=H27
A19	A19	=IF($D$12="","",$D$12)
A20	A20	=IF($D$12="","",$D$12+1)
A21	A21	=IF($D$12="","",$D$12+2)
A22	A22	=IF($D$12="","",$D$12+3)
A23	A23	=IF($D$12="","",$D$12+4)
A24	A24	=IF($D$12="","",$D$12+5)
A25	A25	=IF($D$12="","",$D$12+6)
G19:G25	G19	=IF(B19="Off",0,IF(B19="REGULAR",MROUND((MOD(F19-C19,1)-MOD(E19-D19,1))*24,1/4)))
H19:H25	H19	=G19-I19
H28:L28	H28	=ROUND(H27*H26,2)
K30	K30	=SUM(H28:L28)

Cells with Data Validation
Cell	Allow	Criteria
B19:B25	List	='Data Sheet'!$I$2:$I$13

 

[Trebor76]

Does the attached screen shot reconcile to your expectations?

 

[Robert Wyatt]

Does the attached screen shot reconcile to your expectations?

That looks like what I would like to see. so, what is the formula you used?

 

[Trebor76]

Try these formulas with the given range (I've left the overtime hours in Col. I as they reference another sheet)

G19:G25

=IF(B19="Off",0,MROUND((MOD(F19-C19,1)-MOD(E19-D19,1))*24,1/4))

H19:H25

=IF(B19="Regular",G19-I19,0)

J19:J25

=IF(B19="Off",0,IF(B19="Sick",G19,0))

K19:K25

=IF(B19="Off",0,IF(B19="Holiday",G19,0))

L19:L25

=IF(B19="Off",0,IF(B19="Vacation",G19,0))