You mis-remember the old days.

You could never do that using formatting alone.

Excel 2003 will give you a cell full of ############### same as 2007


Gord Dibben MS Excel MVP


On Sun, 25 Apr 2010 07:30:01 -0700, EmpressTarr
wrote:

In the "old days" you used to be able to type a number into Excel as 11301967
and then use the Format Cells command to have it formatted as a date, such as
11/30/67. In version 2007, I noticed that doing so results in two things,
first the Format Cells (both dates and times are affected) display a sample
string of #### instead of the display of what the date will be formatted as.
Selecting the date format results in width of the cell filling with #, and
displays a tooltip message of "dates and times that are negative or too large
display as #######"

I followed the instructions for one post which advised to use Date/Text to
Columns, which worked fine and resolved the issues (and for both 2 and
4-character years), but can someone explain as to why this occurs?

Thank you.