trouble converting numbers into dates

T

troyandmarsha

i converted a DAT file via the automatic text import
wizard. i have numbers that represent the dates but
excel doesn't see the numbers as date numbers. every time
i try to format the numbers into dates a completely
different formatted date appears. any suggestions?
thanks.

[email protected]
 
N

Norman Harker

Hi!

Can you give examples of what's being imported and what you think it
should be?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
G

Guest

yes, thanks. the numbers coming over into the cell are
10204 which is january 2, 2004. but when i format the
cell to DATE and choose m/dd/yy as the format, then click
OK, it then displays as 12/8/1927. any idea what i'm
missing?

troy
 
C

Chip Pearson

Troy,

In Excel, dates are stored as the number of days since 0-Jan-1900
(1 = 1-Jan-1900, 2 = 2-Jan-1900, etc), so the value 10204 is
treated as 10,204 days since 0-Jan-1900, or 8-Dec-1927.

You should probably skip the text import wizard and write your
own import routine and convert the values appropriately. Some
basic code for importing data can be found at
www.cpearson.com/excel/imptext.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
N

Norman Harker

Hi Troy!

Excel uses a datetime serial number where the serial number is the
number of days since 31-Dec-1899.

You number 10204 is interpreted as 10204 days from 31-Dec-1899 which,
if I recall correctly, was 8-Dec-1927.

It seems that you'll have a mixture of numbers

10204 as 2-Jan-2004
120504 as 5-Dec-2004

You can parse these into dates using:

With the date in A1:
=DATE(RIGHT(A1,2)+2000,IF(LEN(A1)=5,LEFT(A1,1),LEFT(A1,2)),IF(LEN(A1)=
5,MID(A1,2,2),MID(A1,3,2)))
If A1 is 10204 this returns 2-Jan-2004
If A1 is 120505 this returns 5-dec-2004

I'm hoping you don't have dates before 1-Jan-2000 as that complicates
things a tad.

Having converted and checked that all is OK you can:
Select the cells containing formulas
Copy
Edit > Paste Special > Values
OK

The offending original data can now be deleted.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
G

Guest

norman!

it worked! which i'm sure you knew it would, but it
still amazes me. you are king of the day! thanks.

no worries!,
troy
 
N

Norman Harker

Hi Troy!

Thanks for thanks and confirmations are always useful for Google
Searchers. I usually test all replies but there are always mistakes
and often much better ways of doing things.

I'd rather not be King for the day! I'm a Republican and my wife would
only be after some crown jewels <vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
L

lila

-----Original Message-----
i converted a DAT file via the automatic text import
wizard. i have numbers that represent the dates but
excel doesn't see the numbers as date numbers. every time
i try to format the numbers into dates a completely
different formatted date appears. any suggestions?
thanks.

[email protected]
.
Have you tried using customise rather than date format?
Click to expand...
 
Top