convert text "20100315" to date format
in [General]
|
Prev: Excel 2010
Next: IF need to add a third value
From: Terry0928 on 5 Apr 2010 03:30 Hi, I have some data exported from a system, the date is a text displayed as "20100315" which should read yyyymmdd. I need to convert this text to another date format showing mm/dd/yyyy in text for importing into another system. As the text format yyyymmdd does not contain any "/" or "-" so using text to column will be a difficulty. Please advise how could I handle this. Thanks a lot. Best Regards, Terry
From: ozgrid.com on 5 Apr 2010 04:27 =TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mm-dd-yyyy") and copy paste special-values. -- Regards Dave Hawley www.ozgrid.com "Terry0928" <u59143(a)uwe> wrote in message news:a610c18eb8b14(a)uwe... > Hi, > > I have some data exported from a system, the date is a text displayed as > "20100315" which should read yyyymmdd. I need to convert this text to > another date format showing mm/dd/yyyy in text for importing into another > system. As the text format yyyymmdd does not contain any "/" or "-" so > using text to column will be a difficulty. > > Please advise how could I handle this. > > Thanks a lot. > > Best Regards, > Terry >
From: Ms-Exl-Learner on 5 Apr 2010 04:32 Assume that you are having the data like the below: A1 cell 20100315 Copy and paste the below formula in B1 Cell =IF(A1="","",MID(TRIM(A1),5,2)&"/"&RIGHT(TRIM(A1),2)&"/"&LEFT(TRIM(A1),4)) Copy the B1 cell and paste it to the remaining cells of B Column based on the A Column Data. But the above formula will get you the Text Date instead of Real Date. For getting the Real Date Use the below formula:- =IF(A1="","",VALUE(RIGHT(TRIM(A1),2)&"/"&MID(TRIM(A1),5,2)&"/"&LEFT(TRIM(A1),4))) Choose the desired date format from Format Cells. Change the cell reference A1 in the above formula to your desired cell. -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Terry0928" wrote: > Hi, > > I have some data exported from a system, the date is a text displayed as > "20100315" which should read yyyymmdd. I need to convert this text to > another date format showing mm/dd/yyyy in text for importing into another > system. As the text format yyyymmdd does not contain any "/" or "-" so > using text to column will be a difficulty. > > Please advise how could I handle this. > > Thanks a lot. > > Best Regards, > Terry > > . >
From: מיכאל (מיקי) אבידן on 5 Apr 2010 04:58 Assuming the dates are in range A1:A15 and you want to change them in their cells WITHOUT the need for helper column - try to run a small VBA Macro: --------------------- Sub CD() For Each CL In [A1:A15] CL.Value = "'" + Mid(CL, 5, 2) + "/" + Right(CL, 2) + "/" + Left(CL, 4) Next End Sub ------------------- Micky "Terry0928" wrote: > Hi, > > I have some data exported from a system, the date is a text displayed as > "20100315" which should read yyyymmdd. I need to convert this text to > another date format showing mm/dd/yyyy in text for importing into another > system. As the text format yyyymmdd does not contain any "/" or "-" so > using text to column will be a difficulty. > > Please advise how could I handle this. > > Thanks a lot. > > Best Regards, > Terry > > . >
From: Terry0928 on 5 Apr 2010 05:22
Ms-Exl-Learner wrote: >Assume that you are having the data like the below: > >A1 cell >20100315 > >Copy and paste the below formula in B1 Cell > >=IF(A1="","",MID(TRIM(A1),5,2)&"/"&RIGHT(TRIM(A1),2)&"/"&LEFT(TRIM(A1),4)) > >Copy the B1 cell and paste it to the remaining cells of B Column based on >the A Column Data. >But the above formula will get you the Text Date instead of Real Date. > >For getting the Real Date Use the below formula:- > >=IF(A1="","",VALUE(RIGHT(TRIM(A1),2)&"/"&MID(TRIM(A1),5,2)&"/"&LEFT(TRIM(A1),4))) > >Choose the desired date format from Format Cells. > >Change the cell reference A1 in the above formula to your desired cell. > >> Hi, >> >[quoted text clipped - 12 lines] >> >> Hi Ms-Exl-Learner, Thanks for your prompt response. I tried to work the two formula on my data on cell with text "20100201" I tried fomula =IF(AI2="","",MID(TRIM(AI2),5,2)&"/"&RIGHT(TRIM(AI2),2) &"/"&LEFT(TRIM(AI2),4)), it returned 02/01/2010. It works but when I tried to get the value with the fomula =IF(AI2="","",VALUE(RIGHT (TRIM(AI2),2)&"/"&MID(TRIM(AI2),5,2)&"/"&LEFT(TRIM(AI2),4))), it returned #VALUE! I tried to change the fomate of the cell to a date formate but nothing changed. don't understand what is wrong. Please advise. Thanks Terry |