# Excel question

**URL:** <https://boards.straightdope.com/t/excel-question/390554>\
**Category:** Factual Questions\
**Created:** [February 2, 2007, 4:21pm UTC](https://boards.straightdope.com/t/excel-question/390554 "2007-02-02T16:21:23Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Don\_t\_Call\_Me\_Shirley](https://avatars.discourse-cdn.com/v4/letter/d/f4b2a3/32.png) [@Don\_t\_Call\_Me\_Shirley](https://boards.straightdope.com/u/Don_t_Call_Me_Shirley)\
**Post date:** [February 2, 2007, 4:21pm UTC](https://boards.straightdope.com/t/excel-question/390554/1 "2007-02-02T16:21:23Z")

</div>

I am writing an Excel spreadsheet and one of the things I am having it do is calculate the number of days until a future date. So I have a column with a formula to subtract todays date from whatever date I enter in another column. It works fine, the only problem is that the formula column displays -39,155 in every cell all the way down until I fill in a date in the other column. Is there any way to hide the result of the calculation until I fill in the other date and the formula actually calculates a valid answer?

---

<div class="post-metadata">

**Author:** ![muttrox](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@muttrox](https://boards.straightdope.com/u/muttrox)\
**Post date:** [February 2, 2007, 4:39pm UTC](https://boards.straightdope.com/t/excel-question/390554/2 "2007-02-02T16:39:04Z")

</div>

You could just hide the whole column.

Or don’t populate the column with the formula. Wait until a date is entered, and then copy the formula to that row.

---

<div class="post-metadata">

**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\
**Post date:** [February 2, 2007, 4:42pm UTC](https://boards.straightdope.com/t/excel-question/390554/3 "2007-02-02T16:42:51Z")

</div>

It’s a valid answer (2/2/07 = 39,115 for example), but I can see that it doesn’t look nice. It sounds like you want to display the results only if they’re equal to or greater than zero. I’ll poke around and see what I can find out, or one of the Excel gurus will show up. 🙂

---

<div class="post-metadata">

**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\
**Post date:** [February 2, 2007, 4:47pm UTC](https://boards.straightdope.com/t/excel-question/390554/4 "2007-02-02T16:47:36Z")

</div>

OK, here’s one way. Highlight the cells/column and then hit Format–Conditional Formatting. Fill out the conditions to say Cell Value Is – Less Than – 0 – and then hit the Format button. Choose Color of white (assuming the background of the sheet is white). This will “hide” the results less than zero but show zero and greater in black or whatever your text color is.

---

<div class="post-metadata">

**Author:** ![Don\_t\_Call\_Me\_Shirley](https://avatars.discourse-cdn.com/v4/letter/d/f4b2a3/32.png) [@Don\_t\_Call\_Me\_Shirley](https://boards.straightdope.com/u/Don_t_Call_Me_Shirley)\
**Post date:** [February 2, 2007, 4:54pm UTC](https://boards.straightdope.com/t/excel-question/390554/5 "2007-02-02T16:54:34Z")

</div>

[QUOTE=gigi]  
OK, here’s one way. Highlight the cells/column and then hit Format–Conditional Formatting. Fill out the conditions to say Cell Value Is – Less Than – 0 – and then hit the Format button. Choose Color of white (assuming the background of the sheet is white). This will “hide” the results less than zero but show zero and greater in black or whatever your text color is.  
[/QUOTE]

Thanks. That’s perfect.

---

<div class="post-metadata">

**Author:** ![Long\_Time\_Lurker](https://avatars.discourse-cdn.com/v4/letter/l/2bfe46/32.png) [@Long\_Time\_Lurker](https://boards.straightdope.com/u/Long_Time_Lurker)\
**Post date:** [February 2, 2007, 4:57pm UTC](https://boards.straightdope.com/t/excel-question/390554/6 "2007-02-02T16:57:46Z")

</div>

Can also build the condition into a formula. For instance, if the formula that is producing the negative number of days is A1-B1, you could use this: =IF(A1-B1\<0,"",A1-B1).

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [February 2, 2007, 6:01pm UTC](https://boards.straightdope.com/t/excel-question/390554/7 "2007-02-02T18:01:53Z")

</div>

[QUOTE=Long Time Lurker]  
Can also build the condition into a formula. For instance, if the formula that is producing the negative number of days is A1-B1, you could use this: =IF(A1-B1\<0,“”,A1-B1).  
[/QUOTE]  
IMHO this is the preferred solution and much better than mucking around with conditional formatting. With formatting, if someone highlights a block of “empty” cells they will see the large value. Not pretty.

---

<div class="post-metadata">

**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\
**Post date:** [February 2, 2007, 8:47pm UTC](https://boards.straightdope.com/t/excel-question/390554/8 "2007-02-02T20:47:39Z")

</div>

I agree yours is better; the only defense is that when they do see the ugly values, it proves the formula has been copied there, rather than showing nothing in the cell.

---

<div class="post-metadata">

**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\
**Post date:** [February 3, 2007, 12:07am UTC](https://boards.straightdope.com/t/excel-question/390554/9 "2007-02-03T00:07:09Z")

</div>

[QUOTE=Long Time Lurker]  
Can also build the condition into a formula. For instance, if the formula that is producing the negative number of days is A1-B1, you could use this: =IF(A1-B1\<0,“”,A1-B1).  
[/QUOTE]

This is the way I’d do it. Though it has always annoyed me that I need to use one of my seven nested functions to hide unwanted values in a cell.

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [February 3, 2007, 3:39am UTC](https://boards.straightdope.com/t/excel-question/390554/10 "2007-02-03T03:39:39Z")

</div>

[QUOTE=gigi]  
I agree yours is better; the only defense is that when they do see the ugly values, it proves the formula has been copied there, rather than showing nothing in the cell.  
[/QUOTE]  
Depends on how it’s going to be used, and what you are expecting the user to do. If you have a user sophisticated enough to be alarmed at an “empty” cell, and possibly try to type something into it, then they are probably sophisticated enough to see that there is a formula in the formula bar. If you can’t trust the user in this case, I would take the further steps of locking the cells and protecting the sheet.