How to fix Date formatting issue

nshepo20

New Member
Joined
Jun 8, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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! :)
 

Attachments

  • Date issue.PNG
    Date issue.PNG
    12.1 KB · Views: 26

nshepo20

New Member
Joined
Jun 8, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
@nshepo20
You can have the Text to columns run and then apply the formatting to your destination column.
VBA Code:
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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

nshepo20

New Member
Joined
Jun 8, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
How about replacing:

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

with:

VBA Code:
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!!
 

jocote46

New Member
Joined
May 11, 2009
Messages
44
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.
 

Forum statistics

Threads
1,176,138
Messages
5,901,571
Members
434,906
Latest member
Prabhu_Churi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top