Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change satellite julian date format YYYYDDDHHMMSS to excel date ti | Excel Discussion (Misc queries) | |||
I need a yyyymmdd date format with no dashes | Excel Discussion (Misc queries) | |||
Is there an Excel date format as follows: yyyymmdd? | Excel Discussion (Misc queries) | |||
How do you change a date that is in the yyyymmdd format to mmddyy | Excel Discussion (Misc queries) | |||
date type format which is supported by excel 2003 | Charts and Charting in Excel |