# HAve cell show blank if time not entered.

##### Board Regular
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>

 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

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

##### MrExcel MVP
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>

##### Board Regular

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

##### Board Regular

Sorry for the late reply. All formula worked great.

Thanks for your input and help.

Marty

##### MrExcel MVP

Sorry for the late reply. All formula worked great.

Thanks for your input and help.

Marty

You are welcome. Thanks for providing feedback.

Replies
1
Views
175
Replies
1
Views
87
Replies
3
Views
167
Replies
0
Views
1K
Replies
0
Views
587

1,196,013
Messages
6,012,857
Members
441,735
Latest member
melastro

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back