How to format data and keep it automated for future?

[xel_scrub_lord]

Hey everyone,
So I am creating this performance chart for my company wherein I will be importing data from database every month. One of the cells, that has dates, is in the below format. How do I change it to pure date without having to manually changing every cell of that column (there can be upto 10,000 cells in a column). Also I would like it so that it gets updated everytime I copy the new data.

Reported Date: 2/12/2016 (12:35 PM)

Thanks

 

[Legacy 143009]

This should handle:

=SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(A1,")",""),"(",""),10)&MID(SUBSTITUTE(SUBSTITUTE(A1,")",""),"(",""),11,10)," "," ")

 

[Legacy 143009]

This should handle:

=SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(A1,")",""),"(",""),10)&MID(SUBSTITUTE(SUBSTITUTE(A1,")",""),"(",""),11,10)," "," ")

If "Reported Date: " included then:

=SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Reported Date: ",""),")",""),"(",""),10)&MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Reported Date: ",""),")",""),"(",""),11,10)," "," ")

 

[Legacy 143009]

A little fix:

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Reported Date: ",""),")",""),"(",""),10)&MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Reported Date: ",""),")",""),"(",""),11,10)

 

[lhartono]

Another Option,

[COLOR=#d3d3d3]'[/COLOR]
[COLOR=#0000cd]=TEXT(TEXT(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"General"),"mm/dd/yyyy hh:mm AM/PM")[/COLOR]

[COLOR=#ff0000]or[/COLOR]

[COLOR=#0000cd]=TEXT(TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Reported Date: ",""),"(",""),")",""),"General"),"mm/dd/yyyy hh:mm AM/PM")[/COLOR]

 

[Legacy 143009]

Another Option,

[COLOR=#d3d3d3]'[/COLOR]
[COLOR=#0000cd]=TEXT(TEXT(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"General"),"mm/dd/yyyy hh:mm AM/PM")[/COLOR]

[COLOR=#ff0000]or[/COLOR]

[COLOR=#0000cd]=TEXT(TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Reported Date: ",""),"(",""),")",""),"General"),"mm/dd/yyyy hh:mm AM/PM")[/COLOR]

I think your formula results different when the value is 12/12/2016 and 2/12/2016

 

[FormR]

Hi, welcome to the forum!

How do I change it to pure date without having to manually changing every cell of that column (there can be upto 10,000 cells in a column).

With a formula you could use:

Excel 2013/2016
	A	B
1	Report Date: 02/12/2016 (12:35 PM)	02/12/2016 12:35
Sheet1

Cell Formulas
Range	Formula
B1	=0+SUBSTITUTE(SUBSTITUTE(MID(A1,14,21),")",""),"(","")

To convert the values in place you could do a find and replace:

Find "Report Date: " replace with nothing
Find "(" replace with nothing
Find ")" replace with nothing

The macro recorder will give you the code for these actions if you wanted.

 

[Legacy 143009]

Hi, welcome to the forum!



With a formula you could use:

Excel 2013/2016

	A	B
Report Date: 02/12/2016 (12:35 PM)

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]02/12/2016 12:35[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=0+SUBSTITUTE(SUBSTITUTE(MID(A1,14,21),")",""),"(","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




To convert the values in place you could do a find and replace:

Find "Report Date: " replace with nothing
Find "(" replace with nothing
Find ")" replace with nothing

The macro recorder will give you the code for these actions if you wanted.

It is "Reported Date: ". With a little fix the formula above will work:
=0+SUBSTITUTE(SUBSTITUTE(MID(A1,16,21),")",""),"(","")

 

[FormR]

It is "Reported Date: "

Thanks for correcting

 

[Vinoth_Shob]

Trim(left(a1,10))