HAve cell show blank if time not entered.

madforgolf

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

*ABCDEFGHIJK
8Sunday10-Mar9:0015:007:15**13:155:306:000:30
9Monday11-Mar9:0015:007:10**13:055:255:550:30
10Tuesday12-Mar******#VALUE!*#N/A
11Wednesday13-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
CellFormula
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

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>
 

madforgolf

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

Some videos you may like

This Week's Hot Topics

Top