HAve cell show blank if time not entered.

[madforgolf]

Below i have the issue where if i dont work or dont enter a time start / finish it shows the errors in column I. The formula is taken from the helper column J as a table lookup is used to allow breaks. How do i get it to not show the #Value in "I" if no time is entered in (E:H)
I have tried changing =IF(E10="",""=VLOOKUP(J10,$M$1:$N$4,2,TRUE)) in K10 but to no avail.

Thanks
Marty

Sheet1

*	A	B	C	D	E	F	G	H	I	J	K
8	Sunday	10-Mar	9:00	15:00	7:15	*	*	13:15	5:30	6:00	0:30
9	Monday	11-Mar	9:00	15:00	7:10	*	*	13:05	5:25	5:55	0:30
10	Tuesday	12-Mar	*	*	*	*	*	*	#VALUE!	*	#N/A
11	Wednesday	13-Mar	*	*	*	*	*	*	#VALUE!	*	#N/A

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 65.6px;"><col style="width: 60.8px;"><col style="width: 65.6px;"><col style="width: 65.6px;"><col style="width: 44px;"><col style="width: 44px;"><col style="width: 44px;"><col style="width: 44px;"><col style="width: 65.6px;"><col style="width: 43.2px;"><col style="width: 43.2px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B8 =SUM(B7+1) I8 =+J8-K8 J8 =IF(E8=0,"",(F8-E8)+(H8-G8)) K8 =VLOOKUP(J8,$M$1:$N$4,2,TRUE) B9 =SUM(B8+1) I9 =+J9-K9 J9 =IF(E9=0,"",(F9-E9)+(H9-G9)) K9 =VLOOKUP(J9,$M$1:$N$4,2,TRUE) B10 =SUM(B9+1) I10 =+J10-K10 J10 =IF(E10=0,"",(F10-E10)+(H10-G10)) K10 =IF(E10="",""=VLOOKUP(J10,$M$1:$N$4,2,TRUE)) B11 =SUM(B10+1) I11 =+J11-K11 J11 =IF(E11=0,"",(F11-E11)+(H11-G11)) K11 =VLOOKUP(J11,$M$1:$N$4,2,TRUE) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[Aladin Akyurek]

Maybe the following modifications would help...

B8	=B7+1
I8	=IF(COUNT(J2:K8)=2,J8-K8,"")
J8	=SUM(IF(COUNT(E8:F8)=2,F8-E8,0),IF(COUNT(G8:H8)=2,H8-G8,0))
K8	=IF(N(J8),VLOOKUP(J8,$M$1:$N$4,2,TRUE),0)

<tbody>
</tbody>

 

[madforgolf]

Hiya Aladin,

I have altered the 1st two and they have taken away the flaw i had. I will check the other two later tonight.

Cheers and thank you.

Marty

 

[madforgolf]

Hiya Aladin,

Sorry for the late reply. All formula worked great.

Thanks for your input and help.

Marty

 

[Aladin Akyurek]

Hiya Aladin,

Sorry for the late reply. All formula worked great.

Thanks for your input and help.

Marty

You are welcome. Thanks for providing feedback.