Forum Discussion
How to get a cell with a formula to ignore another cell with a formula that shows up blank
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!
7 Replies
- 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 <>"".
- 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.
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.
That's odd. I'd have to see the sheet. You could try this one:
[<>0]mmm dd yyyy;[=0]""
