Date format changing ...

[adambc]

I've got a SOURCE table (refreshed by a Power Query) that has two date columns which are correctly formatted as UK dates ie dd/mm/yyyy ...

Date Created	Date Last Updated
10/09/2024	18/09/2024
05/07/2024	18/09/2024
27/06/2024	18/09/2024
16/09/2024	18/09/2024
16/09/2024	18/09/2024
17/09/2024	18/09/2024

Using the following VBA I am using this table to update a TARGET table ...

Dim wsS As Worksheet: Set wsS = Worksheets("SOURCE")
Dim wsT As Worksheet: Set wsT = Worksheets("TARGET")
Dim sArr, tArr, finArr, i As Long, r As Long, t As Long
Dim SlRow As Long, TlRow As Long, lRow As Long, ct As Long
Dim writ As Boolean
    
    Application.ScreenUpdating = False
    
    SlRow = wsS.Cells(Rows.Count, 1).End(xlUp).Row
    TlRow = wsT.Cells(Rows.Count, 1).End(xlUp).Row
    
    If SlRow > TlRow Then
        lRow = SlRow + SlRow
    Else
        lRow = SlRow + TlRow
    End If

    sArr = wsS.Range("A2:L" & SlRow)
    tArr = wsT.Range("A3:M" & lRow)
    
    For i = 1 To UBound(sArr)
        writ = False
        For r = 1 To UBound(tArr)
            If sArr(i, 2) = tArr(r, 2) Then
                For t = 8 To 12
                    tArr(r, t) = sArr(i, t)
                    tArr(r, 13) = "True"
                    writ = True
                Next
            End If
        Next
        If writ = False Then
            
            tArr(UBound(sArr) + i, 2) = sArr(i, 2)
            tArr(UBound(sArr) + i, 13) = "True"
            For t = 6 To 12
                tArr(UBound(sArr) + i, t) = sArr(i, t)
            Next
        End If
    Next
    ct = 1
    ReDim finArr(1 To UBound(tArr, 1), 1 To UBound(tArr, 2))
    For i = 1 To UBound(tArr)
        If tArr(i, 13) = "True" Then
            For r = 1 To 13
                finArr(ct, r) = tArr(i, r)
            Next
            ct = ct + 1
        End If
    Next
    wsT.Range("A3:L" & TlRow).Clear
    wsT.Range("A3").Resize(UBound(finArr, 1), UBound(finArr, 2) - 1) = finArr
    
    Application.ScreenUpdating = True

But the result looks like this ie the first row has changed to US format and is right justified (despite showing a format as dd/mm/yyyy) and the second row has also changed to US format with month name ...

Interestingly the Date Last Updated column doesn't have this problem?!!!

Date Created	Date Last Updated
09/10/2024​	18/09/2024
07 May 2024	18/09/2024
27/06/2024	18/09/2024
16/09/2024	18/09/2024
16/09/2024	18/09/2024
17/09/2024	18/09/2024

I'm sure there's a simple fix, but I can't work out what it is!

Help please ...

 

[RoryA]

I suspect those two source columns are actually text. If you change the format of those columns, do the displayed cell contents change?

 

[adambc]

I suspect those two source columns are actually text. If you change the format of those columns, do the displayed cell contents change?

No!

I've formatted the SOURCE columns as dd/mm/yyyy and the TARGET columns as dd/mm/yyyy but still get US date formats on the first two rows in the first column...

Date Created	Date Last Updated
09/10/2024​	18/09/2024
07/05/2024	18/09/2024
27/06/2024	18/09/2024
16/09/2024	18/09/2024
16/09/2024	18/09/2024
17/09/2024	18/09/2024

Can't figure out why it's only the first two rows in the first column that are affected?!!!

 

[RoryA]

I mean do the displayed contents change in the source columns? If not, then they are text, which would explain what you are seeing. Only the first two rows could be converted to dates in US format (which is what VBA defaults to), so everything else just comes back as text (which is why it is left-aligned).

You need to convert the source data to real date values.

 

[adambc]

I suspect those two source columns are actually text. If you change the format of those columns, do the displayed cell contents change?

Got it!

Needed to format the two columns as dd/mm/yyyy in the Power Query!!!