Date format anomaly from array output

Question

I have a 2D array and have to print parts of it to a sheet, when I do print it to a sheet most dates appear exactly as they do in the locals window. Some of them don't instead appearing as US Date

Sub ConvertDates()
 With Range("G1:G76")
   .NumberFormat = "dd/mm/yyyy"
   .TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, 
_
       Semicolon:=False, Comma:=False, Space:=False, Other:=False, 
FieldInfo _
    :=Array(1, 4), TrailingMinusNumbers:=True
End With

End Sub

If I change the format inside of excel it just rearranges the date into the UK format but the date is wrong. EG 11/06/2019 is transposed from the array as 06/11/2019 but 13/06/2019 is transposed from the array as 13/06/2019 as it should be. Reformatting the cell inside excel has zero effect, it just changes the format of the date it already decides is correct in the US format.

IF i run VBA code to change the format, it accepts the reformat and now displays the correct date. WTF?? See Convertdates() BUT, if I alter the convertdates() code to numberformat = "dd/mm/yyyy" it will display the incorrect date??

if you need the array code I can show it, but this is bizarre.

the dates this anomaly occurs with are 10,11,12 out of a possible range of 10,11,12,13,14,15,16

Dean Dean · Accepted Answer · 2019-06-27T08:38:58

Yes I know your frustration with this. The workaround I found is below. I stored dates from a range to my array iArray. When outputing them to a specified range use the below.

Sheet1.Cells(myRow, myCol).Value = Format$(iArray(aRow, aCol), "\ dd\/mm\/yyyy\")

Additionally you would need to run an adaptation of the below to clean after. The above will keep a space in the front of the date and for some reason when you run VBA on that date it changes back to US.

Sub CleanDates()

    'excel converting uk dates to us after performing action on it with VBA

    Dim strDate As String, strTrim As String
    Dim dRng As Range, dCol As Long, lRow As Long
    Dim i As Long, j As Long

    Set dRng = wsU.Rows(1).Find(What:="Start Dates", LookAt:=xlWhole)

    If Not dRng Is Nothing Then
        dCol = dRng.Column
    Else
        MsgBox "Failed to find the date columns to clean dates. Please consult the developer.", vbCritical
        Exit Sub
    End If

    lRow = wsU.UsedRange.Rows.Count

    For i = 2 To lRow
        For j = dCol To (dCol + 1)

            strDate = wsU.Cells(i, j).Text

            strTrim = Right(strDate, Len(strDate) - 1)

            wsU.Cells(i, j).Value = DateSerial(Year(strTrim), Month(strTrim), Day(strTrim))

        Next j
    Next i

End Sub

Hope it helps with your issue.

Date format anomaly from array output

1 Answers