Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Eqa Eqa is offline
external usenet poster
 
Posts: 52
Default Dates that need to be split into Year/MM/DD

I have a long list of dates in a column that are YYYYMMDD without any / or -
to break up the year, month,day How do I insert these breaks automatically?
Cell formating by date doesn't give me the the correct format. Thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 76
Default Dates that need to be split into Year/MM/DD

Hi Eqa

Select the column, and go DataTextToColumns. Hit Next, Next and on
the third screen, on the right hand side, choose a Date Format of YMD
and click Finish. Hopefully this will correctly convert them.

An alternative formula approach would be:

=TEXT(A1,"0000-00-00")+0

copied down, but TextToColumns is easier.

Hope this helps!

Richard


On 20 Mar, 08:36, Eqa wrote:
I have a long list of dates in a column that are YYYYMMDD without any / or -
to break up the year, month,day How do I insert these breaks automatically?
Cell formating by date doesn't give me the the correct format. Thanks



  #3   Report Post  
Posted to microsoft.public.excel.misc
Eqa Eqa is offline
external usenet poster
 
Posts: 52
Default Dates that need to be split into Year/MM/DD

Thanks Richard. Very helpful.

"RichardSchollar" wrote:

Hi Eqa

Select the column, and go DataTextToColumns. Hit Next, Next and on
the third screen, on the right hand side, choose a Date Format of YMD
and click Finish. Hopefully this will correctly convert them.

An alternative formula approach would be:

=TEXT(A1,"0000-00-00")+0

copied down, but TextToColumns is easier.

Hope this helps!

Richard


On 20 Mar, 08:36, Eqa wrote:
I have a long list of dates in a column that are YYYYMMDD without any / or -
to break up the year, month,day How do I insert these breaks automatically?
Cell formating by date doesn't give me the the correct format. Thanks




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add one year to dates Lee D Excel Worksheet Functions 3 December 11th 06 08:43 PM
how do I convert a dates in a year quarters in a year? Linndek Excel Discussion (Misc queries) 2 May 11th 06 03:33 PM
Insert Dates for Year Gladys Excel Discussion (Misc queries) 7 December 7th 05 08:07 PM
dates in year MLD Excel Worksheet Functions 3 September 29th 05 11:03 PM
How can I use day of year dates in Excel? jbradford721 Excel Discussion (Misc queries) 2 August 18th 05 02:56 PM


All times are GMT +1. The time now is 09:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"