Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Location
    Derry N I
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    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)


    Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
    Complex problems have simple, easy to understand, wrong answers.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default 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)
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Oct 2006
    Location
    Derry N I
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Complex problems have simple, easy to understand, wrong answers.

  4. #4
    Board Regular
    Join Date
    Oct 2006
    Location
    Derry N I
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hiya Aladin,

    Sorry for the late reply. All formula worked great.

    Thanks for your input and help.

    Marty
    Complex problems have simple, easy to understand, wrong answers.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

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

    Quote Originally Posted by madforgolf View Post
    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.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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