arrays - How to change Date Format to ddmmyyyy from the mmddyyyy in vba? - Stack Overflow

admin管理员组

文章数量:1345090

i am trying to change the date format to dd/mm/yyyy from the mm/dd/yyyy.

It is weird that that only D2 to D7 has this problem, the rest of the column is fine.

Have tried using: Columns("D").NumberFormatLocal = "dd/mm/yyyy" but it does not work.

The code get all the clock in and out date and time of a specific staff from the main Data to Column A & B.

The next line of code get the first clock in timing and last clock out timing of that day. This is done by using scripting dictionary.

However, my date not sure why it has become like this.

Dim oDicMin As Object, oDicMax As Object, rngData As Range
Dim i As Long, vKey, vTmp
Dim arrData
Dim staffPsn As String
Columns("A:B").ClearContents
Range("C2:F50").ClearContents
Range("L2:P22").ClearContents

staffLR = Sheets("All").Cells(Rows.Count, 1).End(xlUp).Row

staffPsn = Sheet1.Range("j6")

counter = 1

For x = 5 To staffLR
 If staffPsn = Sheets("All").Cells(x, 5) Then
    Sheet1.Cells(counter, 1) = CDate(Sheets("All").Cells(x, 1))
    Sheet1.Cells(counter, 2) = CDate(Sheets("All").Cells(x, 3))
    counter = counter + 1
 End If

Next x

Set oDicMin = CreateObject("scripting.dictionary")
Set oDicMax = CreateObject("scripting.dictionary")
' load data into an array
Set rngData = Range("A1").CurrentRegion
arrData = rngData.Value
' loop through data
For i = LBound(arrData) To UBound(arrData)
    vKey = arrData(i, 1) ' Date (Col A) as the key
    vTmp = arrData(i, 2)
    If oDicMin.exists(vKey) Then
        If vTmp < oDicMin(vKey) Then
            oDicMin(vKey) = vTmp ' update Min
        End If
        If vTmp > oDicMax(vKey) Then
            oDicMax(vKey) = vTmp ' update Max
        End If
    Else ' Init. min and max
        oDicMin(vKey) = vTmp
        oDicMax(vKey) = vTmp
    End If
Next i
' header of output
Range("D1:F1").Value = Array("Date", "Clock-In", "Clock-Out")
Columns("E:F").NumberFormatLocal = "[h]:mm:ss"
Columns("C").NumberFormatLocal = "dd/mm/yyyy"


Dim iCnt As Long
iCnt = oDicMin.Count
' write output to sheet
Range("D2").Resize(iCnt, 1) = Application.Transpose(oDicMin.keys)
Range("E2").Resize(iCnt, 1) = Application.Transpose(oDicMin.items)
Range("F2").Resize(iCnt, 1) = Application.Transpose(oDicMax.items)

i am trying to change the date format to dd/mm/yyyy from the mm/dd/yyyy.

It is weird that that only D2 to D7 has this problem, the rest of the column is fine.

Have tried using: Columns("D").NumberFormatLocal = "dd/mm/yyyy" but it does not work.

The code get all the clock in and out date and time of a specific staff from the main Data to Column A & B.

The next line of code get the first clock in timing and last clock out timing of that day. This is done by using scripting dictionary.

However, my date not sure why it has become like this.

Dim oDicMin As Object, oDicMax As Object, rngData As Range
Dim i As Long, vKey, vTmp
Dim arrData
Dim staffPsn As String
Columns("A:B").ClearContents
Range("C2:F50").ClearContents
Range("L2:P22").ClearContents

staffLR = Sheets("All").Cells(Rows.Count, 1).End(xlUp).Row

staffPsn = Sheet1.Range("j6")

counter = 1

For x = 5 To staffLR
 If staffPsn = Sheets("All").Cells(x, 5) Then
    Sheet1.Cells(counter, 1) = CDate(Sheets("All").Cells(x, 1))
    Sheet1.Cells(counter, 2) = CDate(Sheets("All").Cells(x, 3))
    counter = counter + 1
 End If

Next x

Set oDicMin = CreateObject("scripting.dictionary")
Set oDicMax = CreateObject("scripting.dictionary")
' load data into an array
Set rngData = Range("A1").CurrentRegion
arrData = rngData.Value
' loop through data
For i = LBound(arrData) To UBound(arrData)
    vKey = arrData(i, 1) ' Date (Col A) as the key
    vTmp = arrData(i, 2)
    If oDicMin.exists(vKey) Then
        If vTmp < oDicMin(vKey) Then
            oDicMin(vKey) = vTmp ' update Min
        End If
        If vTmp > oDicMax(vKey) Then
            oDicMax(vKey) = vTmp ' update Max
        End If
    Else ' Init. min and max
        oDicMin(vKey) = vTmp
        oDicMax(vKey) = vTmp
    End If
Next i
' header of output
Range("D1:F1").Value = Array("Date", "Clock-In", "Clock-Out")
Columns("E:F").NumberFormatLocal = "[h]:mm:ss"
Columns("C").NumberFormatLocal = "dd/mm/yyyy"


Dim iCnt As Long
iCnt = oDicMin.Count
' write output to sheet
Range("D2").Resize(iCnt, 1) = Application.Transpose(oDicMin.keys)
Range("E2").Resize(iCnt, 1) = Application.Transpose(oDicMin.items)
Range("F2").Resize(iCnt, 1) = Application.Transpose(oDicMax.items)

• arrays
• excel
• vba
• date
• scripting.dictionary

Share Improve this question Follow edited yesterday James asked yesterday JamesJames 1311 silver badge44 bronze badges 11

• Are all dates entered as date/number or are some entered as text/string? – Shrotter Commented yesterday
• Column D2 to D7 is entered as Date while D8 Onward is General. – James Commented yesterday
• then try setting to general also D2-D7 .. this is not a solution by the way just a quick fix. As to why things got automatically intepreted that way seems related to the fact that the dates in D2-D7 are valid dates in mm/dd/yyyy while the next rows have the first part starting from 13. If you needed to solve the issue via script I think you needed to force the conversion of oDicMin.keys to date but I can't be sure – Diego D Commented yesterday
• Maybe the dates are converted to string when they entered as keys into the dictionarys. – Shrotter Commented yesterday
• 2 How was the data entered ? Is is from opening a csv ? – CDP1802 Commented yesterday

 |  Show 6 more comments

1 Answer 1

Sorted by: Reset to default 0

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Set ws = Sheet1 '<~~ Change this to the relevant sheet
    
    Dim dateParts() As String
    Dim lRow As Long, i As Long
    Dim dayPart As Integer, monthPart As Integer, yearPart As Integer
    Dim fixedDate As Date
        
    With ws
        lRow = .Range("D" & .Rows.Count).End(xlUp).Row
            
        For i = 2 To lRow
            If InStr(.Range("D" & i).Value, "/") > 0 Then
                dateParts = Split(.Range("D" & i).Value, "/")
                    
                If UBound(dateParts) = 2 Then
                    monthPart = Val(dateParts(0))
                    dayPart = Val(dateParts(1))
                    yearPart = Val(dateParts(2))
    
                    '~~> If month > 12, it’s probably already
                    '~~> in dd/mm/yyyy format, so skip
                    If monthPart <= 12 And dayPart > 12 Then
                        '~~> Treat it as MM/DD/YYYY
                        '~~> and flip to DD/MM/YYYY
                        fixedDate = DateSerial(yearPart, monthPart, dayPart)
                            
                        .Range("D" & i).Value = fixedDate
                            
                        '<~~ Forcing excel to use literal slashes
                        .Range("D" & i).NumberFormat = "dd\/mm\/yyyy"
                    End If
                End If
            End If
        Next i
    End With
End Sub

Before

After

本文标签： arraysHow to change Date Format to ddmmyyyy from the mmddyyyy in vbaStack Overflow