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.

 

[Robert Wyatt]

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))

this worked great thanks

 

[Trebor76]

this worked great thanks

You're welcome I'm glad we got there in the end

Happy New Year!!