How to fix Date formatting issue

[nshepo20]

Hi all, I need a bit of help with how to fix my date formats. I receive data weekly and I run VBA code that copies the data and paste values it into a master sheet.
I also have an xlookup function referencing that date column, however, the date is coming is as half in one format and the other half in another format... so the xlookup is only working for half of the dates.

I currently use this line in my VBA code but it doesn't change anything -> Sheets("XXX").Range("P:P").NumberFormat = "m/d/yyyy"

Please see the attached picture of the filtered column of dates to understand my issue... I would like the date to be in the format of the others that are MM/DD/YYYY when I run the VBA code so I don't have to manually fix.

Any idea why this is happening and how to format them the same with VBA if possible?

Thank you for your help!

 

[nshepo20]

@nshepo20
You can have the Text to columns run and then apply the formatting to your destination column.

Range("P:P").TextToColumns Destination:=Range("P:P"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
Columns("P:P").NumberFormat = "mm/dd/yyyy"

This did work, thank you!

 

[nshepo20]

How about replacing:

Sheets("XXX").Range("P:P").NumberFormat = "m/d/yyyy"

with:

With Sheets("XXX").Range("P:P")
    .Value = .Value
End With

This worked! Will mark yours as the answer because it is the simplest... thank you!!

 

[johnnyL]

Glad we could help you @nshepo20.

 

[jocote46]

Thank you Team, reading thru the treads i was able to find a solution to an issue that i was having in formatting a date column using VBA, after reading some of the answers and suggestion i was able to find my errors on my code. Just appreciate your guys input and feedback on this problem. thank you for helping the community.