Imported as YYYYMMDD but need Month Day Year

[beginnergirl]

Example import:

20060202

Not sure if it's text or not but need to convert to real date format so that
I can do date calculations.

 

[Ron Coderre]

Try this:

Select the single-column range of "dates"

From the Excel main menu:
<data><text-to-columns>
Click [Next]
Click [Next]
Set the date pattern to: YMD (from the dropdown list in the upper right)
Click [Finish]

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Dave Peterson]

If it's in one column...

Select that column
Data|Text to columns
Fixed width
(Uncheck any lines excel guesses)
Choose ymd (or Ydm???)
and plop it back where you found it.

 

[beginnergirl]

Worked like a charm!!!! "-)

If it's in one column...

Select that column
Data|Text to columns
Fixed width
(Uncheck any lines excel guesses)
Choose ymd (or Ydm???)
and plop it back where you found it.

 

[Bruce Sinclair]

If it's in one column...

Select that column
Data|Text to columns
Fixed width
(Uncheck any lines excel guesses)
Choose ymd (or Ydm???)
and plop it back where you found it.

This raises an interesting question (well, to me anyway ).
The yearmonthdayhour ... date and time format is probably the least
ambiguous of any of them (and I think is the SI standard). Are there any
spreadsheet packages, XL macros or similar to handle them yet ?
Seems odd to have an apparently excellent standard that no one is using ...
well it does to me.

Thanks.

 

[Dave Peterson]

I don't use any other spreadsheet program besides excel and I've never seen
anything built into it that will automatically convert things like that into
dates--with the exception of the text import wizard and data|text to columns.

And macros can be written to do almost anything you want--I wouldn't be
surprised if there is an addin that has this as a feature--but I'm not aware of
it.

 

[KL]

Hi Bruce,

if you have 200602021655, the following formula may work for you^

=--TEXT(A1,"0000-00-00 00\:00")

if the number is in text format, then try

=--TEXT(--A1,"0000-00-00 00\:00")

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

I don't use any other spreadsheet program besides excel and I've never seen
anything built into it that will automatically convert things like that into
dates--with the exception of the text import wizard and data|text to columns.

And macros can be written to do almost anything you want--I wouldn't be
surprised if there is an addin that has this as a feature--but I'm not aware of
it.

I forgot the text import function and yes, that would certainly work for
external data. I suppose that a few lefts/mids etc would let you pull out
the date from a date/time already as text in that format too when I think
about it
That said, I would still be interested if anyone knows of any add ins or
similar to use that format as dates/times.

Thanks

 

[Bruce Sinclair]

I don't use any other spreadsheet program besides excel and I've never seen
anything built into it that will automatically convert things like that into
dates--with the exception of the text import wizard and data|text to columns.

And macros can be written to do almost anything you want--I wouldn't be
surprised if there is an addin that has this as a feature--but I'm not aware of
it.

I forgot the text import function and yes, that would certainly work for
external data. I suppose that a few lefts/mids etc would let you pull out
the date from a date/time already as text in that format too when I think
about it
That said, I would still be interested if anyone knows of any add ins or
similar to use that format as dates/times.

Thanks

 

[Dave Peterson]

The first one worked ok for me -- text or number.

Hi Bruce,

if you have 200602021655, the following formula may work for you^

=--TEXT(A1,"0000-00-00 00\:00")

if the number is in text format, then try

=--TEXT(--A1,"0000-00-00 00\:00")

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

I forgot the text import function and yes, that would certainly work for
external data. I suppose that a few lefts/mids etc would let you pull out
the date from a date/time already as text in that format too when I think
about it
That said, I would still be interested if anyone knows of any add ins or
similar to use that format as dates/times.

Thanks

 

[Bruce Sinclair]

Hi Bruce,

if you have 200602021655, the following formula may work for you^

=--TEXT(A1,"0000-00-00 00\:00")

if the number is in text format, then try

=--TEXT(--A1,"0000-00-00 00\:00")

Thanks, but what I'm really after is "native handling" of this SI (I think)
standard format. XL handles many and various formats including international
ones ... but stores dates/times as a number and not the text string
standard. I would be really interested in being able to "do" functions on it
like add/subtract dates without having to create these functions.

Thinking about it a bit more, I suppose this is only likely to happen if the
business community ask for it ... so not very likely at all I suspect.

Many thanks to all that replied.

 

[KL]

yep, you're right. should have tested it first thanks Dave.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

The first one worked ok for me -- text or number.

Hi Bruce,

if you have 200602021655, the following formula may work for you^

=--TEXT(A1,"0000-00-00 00\:00")

if the number is in text format, then try

=--TEXT(--A1,"0000-00-00 00\:00")

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

I don't use any other spreadsheet program besides excel and I've never seen
anything built into it that will automatically convert things like that into
dates--with the exception of the text import wizard and data|text to columns.

And macros can be written to do almost anything you want--I wouldn't be
surprised if there is an addin that has this as a feature--but I'm not aware of
it.

I forgot the text import function and yes, that would certainly work for
external data. I suppose that a few lefts/mids etc would let you pull out
the date from a date/time already as text in that format too when I think
about it
That said, I would still be interested if anyone knows of any add ins or
similar to use that format as dates/times.

Thanks


Bruce Sinclair wrote:

If it's in one column...

Select that column
Data|Text to columns
Fixed width
(Uncheck any lines excel guesses)
Choose ymd (or Ydm???)
and plop it back where you found it.

beginnergirl wrote:

Example import:

20060202

Not sure if it's text or not but need to convert to real date format so
that
I can do date calculations.

This raises an interesting question (well, to me anyway ).
The yearmonthdayhour ... date and time format is probably the least
ambiguous of any of them (and I think is the SI standard). Are there any
spreadsheet packages, XL macros or similar to handle them yet ?
Seems odd to have an apparently excellent standard that no one is using ...
well it does to me.

Thanks.