How to get a cell with a formula to ignore another cell with a formula that shows up blank

Brass Contributor

Hello.

I am using a Custom Formula in Column B, C, G and H, to format how the date will show on an excel Gantt Chart. My aim is for Column B to calculate 5 days prior to what is in Column C (example: Cell B2 formula is: =C2-5). Here is the problem:

  • Column C is grabbing whatever is in Column H (example Cell C2 formula is =H2). 
  • When a cell is blank in Column H, it shows up in Column C as "Jan 00 1900" unless I make the worksheet's advance option to ignore zeros (which I prefer) then it is blank.
  • When a cell is blank in Column H causing Column C yields the results as mentioned above, Column B shows numerous number signs (example: #######). I would prefer it not to show any results.

Is there a formula that I can write in the Column B cells that ignores the blank cells in Column C (which really aren't blank because there is a formula in them with a custom Date setting) and leaves the cells in Column B blank as well? Please find my sample of the sheet below.

Thanks so much!

 

Asparagus_0-1667330928717.png

 

7 Replies

@Asparagus 

Use this cell formatting code for your dates in C:
mmm dd yyyy;;;

Thanks, that is the formatting I have in place already. I think because it is a custom date setting it see it as a data entry, I am not sure. I know that if I ignore zeros on the sheet all the Jan 00 1900 disappear yet it still causes the B column to show hashtags.

@Asparagus 

That's odd. I'd have to see the sheet. You could try this one:
[<>0]mmm dd yyyy;[=0]""

You can quantify any blank cell using "". I like to use an IFERROR in some formula cells. The make it equal a blank "" if an error in the cell. Then in your formula add a stipulation not equal to a blank or <>"".
That was great for removing all the Jun 00 1900 from the Cells in Column C leaving blank, but still the respective Cells in Column B have the hashtags : (

Is there a way to post the sheet for you to view? I will gladly do so.
Thanks, I have tried that solution as well, I saw it online, but I think the Column B cell see it as text in the cell even thought the formulas in Column C renders them as empty.

That's just a guess. I wish I had a deeper understanding, uggh.
The ####### indicate the column is not wide enough to show contents OR an invalid cell formatting code is being used. You may not have the clearance level yet to attach workbooks, but you can do so if you message me.