Date format issue

K

kkondrat1

Hello,

I have date's that come to me in this format:

20040424
20040426
20040428

how can I convert them so EXCEL can read that as a date?

Note:

Excel does not recognize that format when you format/cells/number/date

Thank
 
B

Bob Phillips

No, Excel will se it as text or as a number. You will need to transform it
into a new column

=Date(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

Or you could do this one column at a time:

Data|text to columns
Fixed width (but don't keep any lines and don't add any)
tell xl it's a date in ymd format.

And put it right back where it came from.
 
R

Ron Rosenfeld

Hello,

I have date's that come to me in this format:

20040424
20040426
20040428

how can I convert them so EXCEL can read that as a date?

Note:

Excel does not recognize that format when you format/cells/number/date

Thanks
Click to expand...

Assuming the dates are in a column:

1. Select the column
2. Data/Text to Columns/Next/Next
Column Data Format Date: YMD
Finish


--ron
 
R

RagDyer

Actually Dave, you can do
<Next> <Next>
click "Date",
Then tell XL what date order the numbers are presently in by choosing from
the drop down format box,
And then <Finish>

It seems to work in either "Delimited" or "FixedWidth".

But, if you give XL the wrong info on the existing format, nothing happens,
and you think it's not working.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Or you could do this one column at a time:

Data|text to columns
Fixed width (but don't keep any lines and don't add any)
tell xl it's a date in ymd format.

And put it right back where it came from.
 
D

Dave Peterson

I picked one (for simplicity--or just because that's the way I always do
it????).

But be careful. Excel could be converting values to dates for things that are
valid dates ymd or ydm.

20040504

could be seen as May 4, 2004 or April 5, 2004.

You'll want to be careful when you choose the order of the date.
 
R

RagDyer

But, that's where the user has to know where he's at!

However, if the column is filled with mixed data, where some are very
obviously "unmatchable" to the format that you told XL to follow, you will
get a mixed column ... the proper ones transformed to dates, and the
"unmatchables" remaining as they were.

I just wonder why XL defaults to that particular date format, m/d/yy, no
matter which order the originals were at?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------




I picked one (for simplicity--or just because that's the way I always do
it????).

But be careful. Excel could be converting values to dates for things that
are
valid dates ymd or ydm.

20040504

could be seen as May 4, 2004 or April 5, 2004.

You'll want to be careful when you choose the order of the date.
 
R

RagDyer

FWIW,

Just took the OP's data and copied it to a column, and then formatted the
column to
dd-MMM-yy
And received a column of ######## (nothing to do with a too narrow column)
But, since the original data was visible in the formula bar, continued right
along with
Data T to C
<Next> <Next>
<Date> <ymd> <Finish>
And got returned the dates in the stipulated format (dd-MMM-yy)!

--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

But, that's where the user has to know where he's at!

However, if the column is filled with mixed data, where some are very
obviously "unmatchable" to the format that you told XL to follow, you will
get a mixed column ... the proper ones transformed to dates, and the
"unmatchables" remaining as they were.

I just wonder why XL defaults to that particular date format, m/d/yy, no
matter which order the originals were at?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------




I picked one (for simplicity--or just because that's the way I always do
it????).

But be careful. Excel could be converting values to dates for things that
are
valid dates ymd or ydm.

20040504

could be seen as May 4, 2004 or April 5, 2004.

You'll want to be careful when you choose the order of the date.
 
D

Dave Peterson

remember that dates are nothing more than days since a starting date (12/31/1899
for most windows users).

20041231 is about 54k years after that.
 
Top