From: Terry0928 on
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
=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
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
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
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

 |  Next  |  Last
Pages: 1 2 3
Prev: Excel 2010
Next: IF need to add a third value