Removing text inbeded in between a date

[JGarces]

Hello group,

Not sure if this has been already addressed as I was unable to find it as a post, but I'm working on creating a report that uses the date. Unfortunately, when I download the data, the dates in column B have the time stamp in between the day/month and the year

Here is an example

Release Date
Aug 16 15:38:37 EDT 2017
Aug 15 12:54:06 EDT 2017
Aug 16 13:06:17 EDT 2017
Aug 16 11:01:28 EDT 2017
Aug 16 13:07:10 EDT 2017
Aug 16 09:25:49 EDT 2017
Aug 11 00:10:25 EDT 2017
Aug 15 15:39:17 EDT 2017
Aug 10 10:24:12 EDT 2017
Aug 10 10:24:12 EDT 2017
Aug 10 09:41:20 EDT 2017
Aug 09 10:46:55 EDT 2017
Aug 09 09:48:08 EDT 2017
Aug 07 09:53:01 EDT 2017
Aug 03 10:24:06 EDT 2017
Aug 07 11:07:40 EDT 2017

Right now the file has about 200 lines, but it will be expanding as it will be a daily report.

I'm not very good at VBA, so this has been a really headache so far. How do you remove the time stamps that is bedded between the date leaving only the dates as mm/dd/yyyy as non-text format. Any suggestions are really appreciated.



Many thanks - Juan

[63falcondude]

With the text dates in column A starting in A2, try this formula in B2

=DATEVALUE(LEFT(A2,6)&", "&RIGHT(A2,4))+MID(A2,8,8)

[AlKey]

Try this

Please Login or Register  to view this content.

Format as Date

v	A	B
1	Aug 16 15:38:37 EDT 2017	8/16/2017
2	Aug 15 12:54:06 EDT 2017	8/15/2017
3	Aug 16 13:06:17 EDT 2017	8/16/2017
4	Aug 16 11:01:28 EDT 2017	8/16/2017
5	Aug 16 13:07:10 EDT 2017	8/16/2017
6	Aug 16 09:25:49 EDT 2017	8/16/2017
7	Aug 11 00:10:25 EDT 2017	8/11/2017
8	Aug 15 15:39:17 EDT 2017	8/15/2017
9	Aug 10 10:24:12 EDT 2017	8/10/2017
10	Aug 10 10:24:12 EDT 2017	8/10/2017
11	Aug 10 09:41:20 EDT 2017	8/10/2017
12	Aug 09 10:46:55 EDT 2017	8/9/2017
13	Aug 09 09:48:08 EDT 2017	8/9/2017
14	Aug 07 09:53:01 EDT 2017	8/7/2017
15	Aug 03 10:24:06 EDT 2017	8/3/2017
16	Aug 07 11:07:40 EDT 2017	8/7/2017

[Root_]

@JGarces:

If you need a formula, try this: =--REPLACE(B2,7,13,",")

If you need a VBA code to convert the entire column, try this:

Please Login or Register  to view this content.

[JGarces]

Thanks to all for the replies, it was really helpful