On Fri, 11 Jan 2013 19:54:24 +0000, JonathanK1 wrote:


This is driving me batty. I hope someone can help. This is excel 2010.


When I type in 01011999 in a cell, then switch it to dates (no matter
which format I choose, it comes out as different numbers. It'll show as
01/26/7435 or something crazy...I tried custom etc. What I want is for
it to show up as 01/01/1999 - for the entire columb. Shouldn't be
hard...yet I've tried almost everything. I even opened a new
spreadsheet and started from scratch.

Or the numbers will switch from 01011999 (which I want to read as
01/01/1999) to ##########.

When you enter a date, you need to enter it with appropriate separators. Formatting only affects how values are displayed, not how they are parsed when they are entered.
Dates are stored as serial numbers with 1 = 1 Jan 1900. So 01011999 is 1,011,999 days since 1 Jan 1900 and should show up as Monday, October 03, 4670

If you want to enter a number without a separator, and have it interpreted as a date, you need to do some "math" to convert it into a real date. This can be done either with VBA or with a formula.

Assuming you want a format of mm/dd/yyyy, you could enter 01011999 in A1 and use this formula:

B1: =--TEXT(A1,"00\/00\/0000") and format B1 as mm/dd/yyyy