Split combined date and time cell to two separate cells and format accordingly vba?

[robdav]

Hi

I have a report which exports the date and time in one cell (column A). I would like to be able to split these via a macro into two cells and then change the date value so it is recognised as a date. Currently the date is delimited by dots and not recognised by Excel.

I would like the date to remain in column A with a newly inserted column B containing the time (there is existing data in column B).

The existing data looks like below with Date & Time in column A and Record ID in column B.

I have hopefully shared a link to some sample data.

Any help would be appreciated.

Thanks

Rob

[TABLE="width: 208"]
<tbody>[TR]
[TD]Date & Time[/TD]
[TD]Record ID[/TD]
[/TR]
[TR]
[TD]2019.02.04 11:36:34[/TD]
[TD]11048383[/TD]
[/TR]
[TR]
[TD]2019.02.07 11:34:11[/TD]
[TD]11214207[/TD]
[/TR]
[TR]
[TD]2019.02.07 11:40:42[/TD]
[TD]11214748[/TD]
[/TR]
[TR]
[TD]2019.02.07 11:40:43[/TD]
[TD]11214749[/TD]
[/TR]
[TR]
[TD]2019.02.07 11:44:59[/TD]
[TD]11215079[/TD]
[/TR]
</tbody>[/TABLE]

https://drive.google.com/file/d/1-_Rx0JLW1mH6ILupMSmo1yCpk-58nwou/view?usp=sharing

 

[JerryH09]

try
=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
Cheers
JerryH09

 

[Gerald Higgins]

And if that doesn't work, try

=DATE(LEFT(A1,4)+0,MID(A1,6,2)+0,MID(A1,9,2)+0)