Vba dd-mm-yyyy

[Rana2014]

I am trying to write a date in "dd-mm-yyyy" format in excel sheet thru excel-vba. I achieved it using [a2].CurrentRegion.Columns("k").NumberFormat = "dd-mm-yyyy”

and my cell value comes out to be 17-02-2021 , which is good.....

But there is some weird problem... Since I have to iterate the startDate for whole month by everytime adding it by 1, so the next value comes out to be 02-01-2021 instead of 01-02-2021 until 12th of each month 12-02-2021 and from 13th, the month is again changing to two digits 13-02-2021

I use belo vba

Sub WYN()

    Dim r As Range

    Dim a, ms

           

    With CreateObject("Scripting.Dictionary")

        For Each r In Range("A2", Cells(Rows.Count, 1).End(xlUp))

            ms = r.Offset(, 4).Value

            If .Exists(ms) Then

                a = .Item(ms)

                a(1, 12) = a(1, 12) + r.Offset(, 11)

                .Item(ms) = a

            Else

                a = r.Resize(, 13).Value

                .Item(ms) = a

            End If

        Next

        a = Application.Transpose(Application.Transpose(.items))

        Cells(Rows.Count, 1).End(xlUp)(3, 1).Resize(UBound(a), 13) = a

        [a2].CurrentRegion.Offset(1).Delete xlUp

        [a2].CurrentRegion.Columns("j").NumberFormat = "hh:mm:ss"

           [a2].CurrentRegion.Columns("k").NumberFormat = "dd-mm-yyyy”

    End With

End Sub

 

[GlennUK]

cross posted : Date showing mm-dd-yyyy instead of dd-mm-yyyy

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Date showing mm-dd-yyyy instead of dd-mm-yyyy
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.