Format Date With VBA (Remove TimeStamp)

[PatrickW1907]

Hi All

I Wondered if anyone could help.

I have the following Data in Column E which is a CSV export.

I've been trying to build a code which will tidy the data.

In short I want to do the following:

• Remove Spaces from Column E
• Remove Timestamp from Column E
• All should be a UK date but I've noticed when playing with Macros it sometimes convert to US I need to keep it UK.
• The number of rows in the data change so will need last row in there rather than a set range.

All that should remain in Column E is the UK date (no spaces and No Timestamp).


Appreciate the help guys.

Data below

The data aligned to the right is due to a space in the cell and it has the format 00:00:00 compared to those aligned to the left that has 00:00

Date Generated

06/01/2019 00:00​

28/06/2019 00:00

28/06/2019 00:00

28/06/2019 00:00

06/05/2019 00:00​

07/09/2019 00:00​

28/07/2019 00:00

28/06/2019 00:00

28/06/2019 00:00

23/07/2019 00:00

30/07/2019 00:00

28/06/2019 00:00

28/06/2019 00:00

28/06/2019 00:00

07/05/2019 00:00​

17/07/2019 00:00

28/06/2019 00:00

28/06/2019 00:00

07/10/2019 00:00​

28/07/2019 00:00

28/08/2019 00:00

 

[PatrickW1907]

Just be careful with the dates you have, it looks to me that the ones that have moved to the right have already had their month and day reversed.
The date range to me looks to be from 28 Jun to 28 Aug and they are falling outside of that range.

Ideally go back to the source data and run the process again. Perhaps import the dates as text first and then run Jimmy's code on that.

Thanks Alex much appreciated !!