Results 1 to 6 of 6

Thread: needing help with show a cell as blank

  1. #1
    Neophyte gizmo's Avatar
    Join Date
    Jul 2021
    Posts
    3
    Articles
    0
    Excel Version
    16.5 IOS

    needing help with show a cell as blank



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, Extremely new to creating my own excel sheets and am self-taught so please bear with me on this. I am trying to get the highlighted cell to show as blank if the number on the corresponding row under "shift length" is less than 5 hours.
    I have the cell highlighted to show the formula I used to achieve the number under the column "lunch".
    I have tried to use "conditional formatting" to create a new rule, which was:
    =IF(C2>B2+TIME(5,0,0),"")
    That was unsuccessful and I am at a loss. Any help is greatly appreciated, thank you!
    Click image for larger version. 

Name:	Screen Shot 2021-07-18 at 6.06.53 PM.jpg 
Views:	21 
Size:	91.3 KB 
ID:	10567

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,030
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by gizmo View Post
    to get the highlighted cell to show as blank if the number on the corresponding row under "shift length" is less than 5 hours.
    Ditch any conditional formatting and adjust your spreadsheet formula to read:
    =IF(OR(C2="",D2="",C2<5),"",E2+2*(1/24))

    It would be better if you attached a workbook, especially as there are times involved, because what we see in a picture may not tell us what's in the cells.

  3. #3
    Neophyte gizmo's Avatar
    Join Date
    Jul 2021
    Posts
    3
    Articles
    0
    Excel Version
    16.5 IOS
    Thank you so much for responding! This really helps. I wasn't able to get code to work but have attached the workbook. Your help is appreciated. Test Daily Tracker.xlsx

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,030
    Articles
    0
    Excel Version
    365
    In the attached I have only changed the formulae in the yellow highlighted cells.
    Column G formula is, I suspect, looking at the wrong columns.
    Attached Files Attached Files

  5. #5
    Neophyte gizmo's Avatar
    Join Date
    Jul 2021
    Posts
    3
    Articles
    0
    Excel Version
    16.5 IOS
    Quote Originally Posted by p45cal View Post
    In the attached I have only changed the formulae in the yellow highlighted cells.
    Column G formula is, I suspect, looking at the wrong columns.
    Thank you so much! I have been trying to figure this out for about 2 days.

    Curious as to what the $ equates to? I am able to see where I was making the mistakes in regards to the time.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,030
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by gizmo View Post
    Curious as to what the $ equates to?
    Check out absolute v. relative references. I think you tried to copy a formula to the right before tweaking it, but because that formula used relative references it ended up looking at the wrong columns. The $ makes the column reference absolute but the row reference remains relative.

Posting Permissions

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