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 ##########.

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

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 ##########.


I gave up using date fields in MS products (Excel or Access). No matter what
format I typed the date in as, it always displayed the wrong format.


--
Steve Hayes from Tshwane, South Africa
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

Hi Jonathan,

Am Fri, 11 Jan 2013 19:54:24 +0000 schrieb JonathanK1:

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.

you have formatted the cells as text because of the leading zero?
All you numbers have 8 digits?
Then click in the header of the column and choose "TextToColumns" =
Fixed Width = Next = Next = Format of the columns = Date MTY and
finish.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

JonathanK1 wrote on 11/01/2013 :
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 ##########.

<FWIW
Adding to the date issue.., My XP machines accept date input as m/d and
result as "m/d/2013". My Win7 machines require input as d/m in the same
scenario, to result as "d/m/2013". This is controlled by the system
date format AFAIK! Using the keyboard shortcut "Ctrl+;" to auto-enter
the current date works the same in either OS but the result is still
system date format.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion