Thread: HAve cell show blank if time not entered. Thanks: 0 Likes: 0

1. HAve cell show blank if time not entered.

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

 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)

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

2. Re: HAve cell show blank if time not entered.

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)  Reply With Quote

3. Re: HAve cell show blank if time not entered.

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  Reply With Quote

4. Re: HAve cell show blank if time not entered.

Sorry for the late reply. All formula worked great.

Thanks for your input and help.

Marty  Reply With Quote

5. Re: HAve cell show blank if time not entered. Originally Posted by madforgolf Sorry for the late reply. All formula worked great.

Thanks for your input and help.

Marty
You are welcome. Thanks for providing feedback.  Reply With Quote

User Tag List Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•