If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|