Mismatch data - Dates issue for certain range

[manrimo]

Dim S As String
S = Range("G2:G2000").Value
Range("G2:G2000").NumberFormat = "dd/MM/yyyy"
Range("G2:G2000").Value = S

Debug show Mismatch data.
I need to convert all data according to dates format
THis is because, VBA jumble the dates month and days

I had do as below and it was success.
Unfortunately, when i change the script as above, its failed

S = Range("G2").Value
Range("G2").NumberFormat = "dd/MM/yyyy"
Range("G2").Value = S

Please help me.

[Kenneth Hobs]

Welcome to the forum!

The reason the first failed was because you tried to put an array of numbers into a single string variable.

IF the values are dates, those are numbers, not strings. You only need the NumberFormat line.

Value is only needed if you wanted to convert formula results into a static value.

[manrimo]

Hi kenneth,

Thank you for your feedback.
Can you show me a few suggestion so i can refer it.
Im beginner in VBA
Untitled.jpg

I had try 1 line below this, but no effect
suppose the dates will be 9 as a months..not as a dates

Worksheets("PASTE").Range("G2:G2000").NumberFormat = "dd/MM/yyyy"

[Kenneth Hobs]

9 is not a date one would normally use. I guess you could use =Date() to make some day and year with the month 9 as a date.

Dates can have number formats that show just a month, day, day of week or such.

When you get 5 posts you can attach a file.

[manrimo]

actually, the data start turn backwards month to days and year after i had done this

Selection.TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

when im using manual, the dates was find..
but when using the VBA..the problem started
any idea why?

[paulked]

Dates are messy if you live outside of the USA as they use them backwards to other countries. It can be a nightmare at first, but you'll get into it, eventually!

Try this:

Sub test()
    Dim i As Long
    For i = 2 To 2000
        Range("G" & i).NumberFormat = "dd/mm/yyyy" 'or use cells(i, 7).NumberFormat = "dd/mm/yyyy"
    Next
End Sub

[p45cal]

I think your dates are originally in MDY format but as text?
First convert them to the correct dates, then format as you wish.
To convert these strings to true Excel dates:

With Range("G2:G2000")
  .TextToColumns DataType:=xlDelimited, _
                 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                 Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                 :=Array(1, 4), TrailingMinusNumbers:=True
  .NumberFormat = "dd/MM/yyyy"
End With

If they were originally DMY text dates, the only difference to correctly convert to true Excel dates would be to change
Array(1, 4)
to
Array(1, 3)

[Paul_Hossler]

Try this and see

Option Explicit


Sub FixDates()
    Dim r As Range, r1 As Range
    Dim y As Long, m As Long, d As Long
    
    Set r = Range("G2")
    Set r = Range(r, r.End(xlDown))
  
    MsgBox r.Address


    'mm/dd/yyyy
    For Each r1 In r.Cells
        With r1
            m = Left(.Text, 2)
            d = Mid(.Text, 4, 2)
            y = Right(.Text, 2)
            .Offset(0, 1).NumberFormat = "dd/mm/yyyy"
            .Offset(0, 1).Value = DateSerial(y, m, d)
        End With
    Next


End Sub

DateSerial always returns a Date regardless of Region or format, and .Text returns the information as displayed