convert text "20100315" to date format

[Terry0928]

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

 

[ozgrid.com]

=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mm-dd-yyyy")

and copy paste special-values.

 

[Ms-Exl-Learner]

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.

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Terry0928]

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

 

[Terry0928]

=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mm-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.

 

[David Biddulph]

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

 

[מיכ×ל (מיקי) ×בידן]

If I'm not mistaken - you have missed the important part

:
....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]

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

If I'm not mistaken - you have missed the important part

:
...I need to convert this text to another date format showing mm/dd/yyyy
!!!
IN TEXT !!! for importing into another system. [end quote]
Micky

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




.

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

If I'm not mistaken - you have missed the important part

:
...I need to convert this text to another date format showing mm/dd/yyyy
!!!
IN TEXT !!! for importing into another system. [end quote]
Micky

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




.

 

[מיכ×ל (מיקי) ×בידן]

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

If I'm not mistaken - you have missed the important part

:
...I need to convert this text to another date format showing mm/dd/yyyy
!!!
IN TEXT !!! for importing into another system. [end quote]
Micky

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


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]

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.exe>>Regional and Language
Options>>Customize>>Date>>Short Date format>>Check 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)
--------------------

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

.