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

 

[Alex Blakenburg]

Your assessment of what you are looking at seems unlikely.
What is your region setting for the date format ?
The results you are getting are more indicative that your system setting is set to US dates.

Typically the dates to the left are the text ones and the ones to the right are dates.
If you change the date format of the column to say dd-mmm-yyyy, which ones change format ? The ones to the left or the ones to the right.

 

[Jimmypop]

Hi There

Maybe try the following.. and if I did something wrong somewhere please let me know what mistake was...thanks

Option Explicit
Sub Fix_Dates()
    Dim c As Long
    Columns("E:E").Select
    Selection.TextToColumns DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 4), Array(2, 9)), TrailingMinusNumbers:=True
    Selection.NumberFormat = "dd-mm-yyyy"
End Sub

 

[Jimmypop]

Ignore above seeing as I found some mistakes. Hopefully... Date format wrong and Dim c as long should not be there...

Try below updated...

Sub Fix_Dates()
    Columns("E:E").Select
    Selection.TextToColumns DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 4), Array(2, 9)), TrailingMinusNumbers:=True
    Selection.NumberFormat = "mm-dd-yyyy"
End Sub

 

[Alex Blakenburg]

Ignore above seeing as I found some mistakes. Hopefully... Date format wrong and Dim c as long should not be there...

Try below updated...

    Selection.NumberFormat = "mm-dd-yyyy"
End Sub

I think you're post prior to this one was right. UK format is dd-mm-yyyy.

All should be a UK date

 

[Jimmypop]

I think you're post prior to this one was right. UK format is dd-mm-yyyy.

Hi Alex.. Thanks. Thought so... but sometime I get confused with the different formats sometimes... I then become a chameleon on an M & M Packet

 

[PatrickW1907]

Your assessment of what you are looking at seems unlikely.
What is your region setting for the date format ?
The results you are getting are more indicative that your system setting is set to US dates.

Typically the dates to the left are the text ones and the ones to the right are dates.
If you change the date format of the column to say dd-mmm-yyyy, which ones change format ? The ones to the left or the ones to the right.

Thanks Alex

Appreciate your response.

As far as I am aware the region setting is UK but when I have been designing some code previous copying and pasting mainly it pastes in American format lol .

In all other spreadsheets the date is generally in uk format.

And in regards to the second part it is the data that aligns to the right that changes format. Any Ideas how we change the text ones to dates?

 

[PatrickW1907]

Hi There

Maybe try the following.. and if I did something wrong somewhere please let me know what mistake was...thanks

Option Explicit
Sub Fix_Dates()
    Dim c As Long
    Columns("E:E").Select
    Selection.TextToColumns DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 4), Array(2, 9)), TrailingMinusNumbers:=True
    Selection.NumberFormat = "dd-mm-yyyy"
End Sub

Hi Jimmypop Thanks for your response. This code works perfect Thank you soo much !!!

 

[Jimmypop]

Hi Jimmypop Thanks for your response. This code works perfect Thank you soo much !!!

Glad we could have helped... and thanks for the feedback

 

[Alex Blakenburg]

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.