Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dan dan is offline
external usenet poster
 
Posts: 866
Default How do I change date yyyymmdd to a Excel-supported date format?

I have a column of imported data information of the format yyyymmdd, e.g.,
18400314. Excel does not recognize this date format. How do I change it so
that Excel will recognize it?
Many thanks for any help.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default How do I change date yyyymmdd to a Excel-supported date format?

Excel date format will not recognise dates before 1900. I would suggest
that you use Data/ Text to Columns or another method to produce separate
columns for year month and day, and manipulate them separately.
--
David Biddulph

"dan" wrote in message
...
I have a column of imported data information of the format yyyymmdd, e.g.,
18400314. Excel does not recognize this date format. How do I change it so
that Excel will recognize it?
Many thanks for any help.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How do I change date yyyymmdd to a Excel-supported date format?

Do you really have dates before 1900 as your example showed? If so, I don't
think Excel will be able to handle them. If on other hand, that was just a
mistaken year example, then try this procedure... select your column of
numbers, click Date/TextToColumns on Excel's menu bar, click OK twice to get
to Step 3 of 3 and select YMD from the Date drop-down in the upper right
corner of the dialog box, then click Finish. That should turn your numbers
into real dates.

Rick


"dan" wrote in message
...
I have a column of imported data information of the format yyyymmdd, e.g.,
18400314. Excel does not recognize this date format. How do I change it so
that Excel will recognize it?
Many thanks for any help.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 221
Default How do I change date yyyymmdd to a Excel-supported date format?

G'day Dan

The closest I could come to converting this into what resembles a date
format is this

Assume:

18400314 = A3

place this in B3 =RIGHT(A3,2) ' this will give you the Day = 14
place this in C3 =MID(A3,5,2) ' this will give you the Month = 03
place this in D3 =LEFT(A3,4) ' this will give you the year = 1840

To comine them into one

place this in E3 =B3&"/"&C3&"/"&D3 ' this will give you this:

14/03/1840

The only drawback is that this is text, not date.

One of the many MVP Guru's may provide something more suitable.

HTH
Mark.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dan dan is offline
external usenet poster
 
Posts: 866
Default How do I change date yyyymmdd to a Excel-supported date format

Thank you...it worked! I did have years in the 1800s, but I just added the
equivalent of 100 yrs to them to make them 1900s.

dan

"Rick Rothstein (MVP - VB)" wrote:

Do you really have dates before 1900 as your example showed? If so, I don't
think Excel will be able to handle them. If on other hand, that was just a
mistaken year example, then try this procedure... select your column of
numbers, click Date/TextToColumns on Excel's menu bar, click OK twice to get
to Step 3 of 3 and select YMD from the Date drop-down in the upper right
corner of the dialog box, then click Finish. That should turn your numbers
into real dates.

Rick


"dan" wrote in message
...
I have a column of imported data information of the format yyyymmdd, e.g.,
18400314. Excel does not recognize this date format. How do I change it so
that Excel will recognize it?
Many thanks for any help.



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
Change satellite julian date format YYYYDDDHHMMSS to excel date ti putley Excel Discussion (Misc queries) 1 January 11th 08 07:12 PM
I need a yyyymmdd date format with no dashes EXCEL HELP PLEASE Excel Discussion (Misc queries) 2 December 21st 06 09:19 PM
Is there an Excel date format as follows: yyyymmdd? N Excel Discussion (Misc queries) 3 June 22nd 06 09:44 PM
How do you change a date that is in the yyyymmdd format to mmddyy Norton Excel Discussion (Misc queries) 2 March 8th 06 04:15 PM
date type format which is supported by excel 2003 kanchan Charts and Charting in Excel 2 September 21st 05 11:56 PM


All times are GMT +1. The time now is 09:38 PM.

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"