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

=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@uwe wrote in message news:a610c18eb8b14@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

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

.

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

.

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

ozgrid.com wrote:
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"m m-dd-yyyy")

and copy paste special-values.

Hi,

[quoted text clipped - 10 lines]
Best Regards,
Terry

Thanks ozgrid.com
I tried and it works,
Thanks a lot.

=--TEXT(A1,"0000\-00\-00") and format as date to suit.
--
David Biddulph


"Terry0928" <u59143@uwe wrote in message news:a610c18eb8b14@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

If I'm not mistaken - you have missed the important part [quote]:
....I need to convert this text to another date format showing mm/dd/yyyy !!!
IN TEXT !!! for importing into another system. [end quote]
Micky


"David Biddulph" wrote:

=--TEXT(A1,"0000\-00\-00") and format as date to suit.
--
David Biddulph


"Terry0928" <u59143@uwe wrote in message news:a610c18eb8b14@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


.

I had assumed that if the OP wanted it in text for exporting to another
system, he would do so by saving the Excel output as csv or another text
format. I guessed that he knew how to do that, and that his question was
the one he asked: how to "convert this text to another date format".
--
David Biddulph


"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
If I'm not mistaken - you have missed the important part [quote]:
...I need to convert this text to another date format showing mm/dd/yyyy
!!!
IN TEXT !!! for importing into another system. [end quote]
Micky


"David Biddulph" wrote:

=--TEXT(A1,"0000\-00\-00") and format as date to suit.
--
David Biddulph


"Terry0928" <u59143@uwe wrote in message news:a610c18eb8b14@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


.

=TEXT(--TEXT(A1,"0000\-00\-00"),"mm/dd/yyyy")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il wrote in message
...
If I'm not mistaken - you have missed the important part [quote]:
...I need to convert this text to another date format showing mm/dd/yyyy
!!!
IN TEXT !!! for importing into another system. [end quote]
Micky


"David Biddulph" wrote:

=--TEXT(A1,"0000\-00\-00") and format as date to suit.
--
David Biddulph


"Terry0928" <u59143@uwe wrote in message news:a610c18eb8b14@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


.

This is a "different" symphony and answers the OP question.
Micky


"Niek Otten" wrote:

=TEXT(--TEXT(A1,"0000\-00\-00"),"mm/dd/yyyy")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"מיכ�ל (מיקי) �בידן" <micky-a*at*tapuz.co.il wrote in message
...
If I'm not mistaken - you have missed the important part [quote]:
...I need to convert this text to another date format showing mm/dd/yyyy
!!!
IN TEXT !!! for importing into another system. [end quote]
Micky


"David Biddulph" wrote:

=--TEXT(A1,"0000\-00\-00") and format as date to suit.
--
David Biddulph


"Terry0928" <u59143@uwe wrote in message news:a610c18eb8b14@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


.

If your System Date setting in Control Panel is MM-DD-YYYY then the below
formula will work fine.
=IF(A1="","",VALUE(MID(TRIM(A1),5,2)&"/"&RIGHT(TRIM(A1),2)&"/"&LEFT(TRIM(A1),4)))

If your System Date setting in Control Panel is DD-MM-YYYY then the below
formula will work fine.
=IF(A1="","",VALUE(RIGHT(TRIM(A1),2)&"/"&MID(TRIM(A1),5,2)&"/"&LEFT(TRIM(A1),4)))

To check your system Date Format:-
Run Type control.exeRegional and Language
OptionsCustomizeDateShort Date formatCheck whether it is DD-MM-YYYY
or MM-DD-YYYY
In Long Date Format Also. Use the formula based on your Control Panel Date
Setting.

--------------------
(Ms-Exl-Learner)
--------------------


"Terry0928" wrote:

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

.