Date changes to US format when using VBA

[hocohen]

The issue is:

1. I export SAP report to Excel.
2. One of the fields is a Date in Text format "dd.mm.yyyy"
3. In order to convert it to a Date format - I replace the "." into "/", and format the field as date "dd.mm.yyyy".
4. If I do the replacement in Excel, the result is correct "dd/mm/yyyy", but If I do it in VBA - the day and month alternate and the result is "mm/dd/yyyy".

I understand it happens because the VBA format is always US.
The question is if there is a solution for that.

 

[PeteWright]

A simple workaround would be extracting the date parts (day, month, year) from the text String and creating a VBA date from them.

Assuming, you have a date formatted as "dd.mm.yyyy" in cell A1, the following VBA code would create a date object:

Sub txt2date()
   Dim X As Date
   Dim d As String
   Dim m As String
   Dim y As String
  
   d = left(Range("A1").Value, 2)
   m = mid(Range("A1").Value, 4, 2)
   y = Right(Range("A1").Value, 2)
   X = DateSerial(y, m, d)
  
   MsgBox X
End Sub

The Message Box is just for showing the resulting date.
Range() should be used with the correct Workbook and Worksheet like ThisWorksheet.Sheets("Sheet1").Range()

 

[Akuini]

but If I do it in VBA - the day and month alternate and the result is "mm/dd/yyyy".

Usually, using Cdate function is enough to solve this kind of problem.
If not, try using DateSerial:

Sub try()
Dim tx As String
Dim dt As Date
    tx = "02.03.2023"
    a = Split(tx, ".")
    dt = DateSerial(a(2), a(1), a(0))
    Debug.Print dt   'returns 02/03/2023
    Debug.Print Month(dt) 'returns 3
End Sub

Note: My system uses DMY format.

 

[Akuini]

Using Cdate:

Sub try2()
Dim tx As String
Dim dt As Date
    tx = "02.03.2023"
    dt = CDate(Replace(tx, ".", "/"))
    Debug.Print dt   'returns 02/03/2023
    Debug.Print Month(dt) 'returns 3
End Sub

Note: My system uses DMY format.

 

[hocohen]

A simple workaround would be extracting the date parts (day, month, year) from the text String and creating a VBA date from them.

Assuming, you have a date formatted as "dd.mm.yyyy" in cell A1, the following VBA code would create a date object:

Sub txt2date()
   Dim X As Date
   Dim d As String
   Dim m As String
   Dim y As String
 
   d = left(Range("A1").Value, 2)
   m = mid(Range("A1").Value, 4, 2)
   y = Right(Range("A1").Value, 2)
   X = DateSerial(y, m, d)
 
   MsgBox X
End Sub

The Message Box is just for showing the resulting date.
Range() should be used with the correct Workbook and Worksheet like ThisWorksheet.Sheets("Sheet1").Range()

How to use it for a range (column with varies number of rows)?

 

[Joe4]

Please stop duplicate the same response many times!
As per forum rules, posts of a duplicate nature will be deleted.

 

[hocohen]

Akuini​

How to use Cdate for a range (column with varies number of rows)?

 

[Akuini]

Akuini​

How to use Cdate for a range (column with varies number of rows)?

Try:

Sub try4()
Dim tx As String
Dim dt As Date
Dim c As Range
Set c = Range("A2:A10")  'adjust the range
    va = c
    For i = 1 To UBound(va, 1)
        va(i, 1) = CDate(Replace(va(i, 1), ".", "/"))
    Next
    c.NumberFormat = "dd-mm-yyyy"
    c = va
End Sub

 

[hocohen]

Macro stopped at:
For i = 1 To UBound(va, 1)
Type mismatch

 

[Joe4]

I used Akuini's method inside a range loop:

Sub MyDateMacro()

    Dim lr As Long
    Dim cell As Range
   
    Application.ScreenUpdating = False
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all cells in column A starting on row 2
    For Each cell In Range("A2:A" & lr)
        cell = CDate(Replace(cell, ".", "/"))
        cell.NumberFormat = "dd-mm-yyyy"
    Next cell
   
    Application.ScreenUpdating = True
   
End Sub