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!

 

[6StringJazzer]

The format has nothing to do with what you are seeing in your filter list, and XLOOKUP does not care how dates are formatted. However, the filter list does looks to me like some of your data is being pasted as true dates, but other data is being pasted as text. See example below.

Where does your VBA copy the data from?
What format is the data in the source?

Please show your VBA (it will be more readable if you use code tags. After pasting the code, select the code and click the VBA button to add code tags.)

 

[nshepo20]

The format has nothing to do with what you are seeing in your filter list, and XLOOKUP does not care how dates are formatted. However, the filter list does looks to me like some of your data is being pasted as true dates, but other data is being pasted as text. See example below.

Where does your VBA copy the data from?
What format is the data in the source?

Please show your VBA (it will be more readable if you use code tags. After pasting the code, select the code and click the VBA button to add code tags.)

View attachment 53910

View attachment 53911

Hey Jeff, thank you so much for your reply! It does appears that the data in my source file is coming in formatted as some "general" and some "date". The actual numbers look identical though...

Set Found = ws.Range("A1:T1").Find("WEEKSTART") '<== Header name to search for

If Not Found Is Nothing Then
    LRow = ws.Cells(ws.Rows.Count, Found.Column).End(xlUp).Row
    ws.Range(ws.Cells(2, Found.Column), ws.Cells(LRow, Found.Column)).Copy
    Sheets("XXX").Range("P" & LastRow).Offset(1, 0).PasteSpecial xlPasteValues '<== Sheet to paste data
End If

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

Here is my VBA code that copies from the source sheet to my master sheet

I think I maybe just need to apply the .NumberFormat code to the source sheet's "WEEKSTART" column prior to the copy and paste?
But even still, how come it won't format to all be a date in the master sheet? Is it because .NumberFormat only formats a number? If so can the text format be converted to date?

Thank you

 

[6StringJazzer]

I'm trying to get at more detail about where the data comes from. Are you copying it from another Excel file, or from some other application? It doesn't matter what the format type is (General, Date, or anything) if the data itself is text. If you are copying text into a cell, then formatting the cell as .NumberFormat = "m/d/yyyy", it doesn't convert it to a date, it is still text.

I have seen cases where people import data from an application like SAP where it's all text even though it's hard to tell that when you see it in Excel. Then it has to be explicitly converted to dates.

 

[nshepo20]

I'm trying to get at more detail about where the data comes from. Are you copying it from another Excel file, or from some other application? It doesn't matter what the format type is (General, Date, or anything) if the data itself is text. If you are copying text into a cell, then formatting the cell as .NumberFormat = "m/d/yyyy", it doesn't convert it to a date, it is still text.

I have seen cases where people import data from an application like SAP where it's all text even though it's hard to tell that when you see it in Excel. Then it has to be explicitly converted to dates.

Sorry for the confusion, yes it is coming from another excel file that I copy over the sheets to my current file, so the raw data is a separate sheet that I move into the same file. The raw data is coming in as “12/13/2021” but is like have “general” and half “date”. I guess I need to convert the ones that are text to date?

 

[6StringJazzer]

Yes, but that could be done in the code. Is it is possible for you to show us some sample source data?

 

[SpillerBD]

@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"

 

[johnnyL]

How about replacing:

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

with:

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

 

[SpillerBD]

How about replacing:

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

with:

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

That keeps the same value. If it is text, it will remain text, right?

 

[johnnyL]

That keeps the same value. If it is text, it will remain text, right?

Try it and see. It converts the text date to a value date. Ie. it converts a text date that is aligned to the left, to a date that is aligned to the right.