format text to date results in ##############

  • Thread starter Thread starter EmpressTarr
  • Start date Start date
E

EmpressTarr

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.
 
In Excel, dates are really just integer numbers that are formatted to look
like a date.

These numbers are simply the count of days starting from a base date. The
default base date is 1/1/1900. The numeric value of 1/1/1900 is 1. Each
successive day the numeric value increases by 1. So, 1/2/1900 has a numeric
value of 2. 1/3/1900 has a numeric value of 3. Etc., etc., etc. Today's date
is 4/25/2010. 4/25/2010 has the numeric value of 40293. These integer
numbers are also known as the date serial numbers.

11/30/1967 = date serial number 24806.

To see this in "action" enter some random date in a cell. If you enter the
date in a true Excel date format Excel will automatically recognize the
entry as a date and will automatically apply a date format to the entry.
Now, change the format of that cell to General. You'll see the cell now
contains an integer, the date serial number.

The reason you get all those "hash marks" when you try to format the number
11301967 as a date is because Excel sees that as a date serial number *but*
Excel only recognizes dates up to 12/31/9999. 12/31/9999 = date serial
number 2958465. So, the number 11301967 exceeds the largest date serial
number that Excel will evaluate as a date.
 
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
 
Thank you so much for your prompt, professional, and most of all clear and
helpful information.
 
Back
Top