Date changes to ### when next cell is blank - why?

[pamjmac27]

When I enter the date and it is longer than the column I'm working in, it
usually just shows up flowing over the next cell, which is blank. However,
once in awhile instead of showing the date, I get #### instead - even though
the next cell is blank. WHY? I don't want to change the width of the date
cell as this will throw off the rest of the spreadsheet..

 

[David Biddulph]

My guess is that the next cell isn't really blank, but perhaps contains a
space or an empty string. If you don't want to make the column wider,
another option is to make the font size smaller.

The other cause of getting a #### result from what you hope is a date is if
you are trying to show a negative number as date or time. In this case you
may get somewhere by changing to 1904 date format, but beware of
consequences when you try to push data to & fro between sheets with
different date options.

 

[Stephen]

When I enter the date and it is longer than the column I'm working in, it
usually just shows up flowing over the next cell, which is blank.
However,
once in awhile instead of showing the date, I get #### instead - even
though
the next cell is blank. WHY? I don't want to change the width of the
date
cell as this will throw off the rest of the spreadsheet..

The adjacent cell ISN'T blank, then. It may appear blank, but it isn't. It
may contain a space. It may be formatted with white font on white
background. It may contain a value of zero and zero values are set not to be
displayed. It may contain a null text string "". It may contain a formula
returning any of these. There are probably other possibilities also. What's
certain is that it isn't blank!

Try copying one of the 'adjacent' cells in a row where data from the cell to
the left appears over it as you expect (which is therefore definitely blank)
and pasting this onto the cell that you think is blank. You will see that it
makes a difference.

 

[Dave Peterson]

Numbers (including dates) won't overflow into adjacent cells.

Maybe you could change the font.

 

[Dave Peterson]

You may want to use:
Format|Cells|Alignment tab|Check the "Shrink to Fit" box.

If the problem only occurs on certain dates, you may not even not a dramatic
change in font size.

 

[Gord Dibben]

A date is a number.

Numbers won't stretch across into an adjacent even if the adjacent cell is
empty.

If you have dates that stretch across, they are text and not real dates.


Gord Dibben MS Excel MVP

 

[pamjmac27]

That was the answer - thank you. When I change the cell's format to 'Text'
it works perfectly.