Date format in Excel

Question

Hi. I have a question related to date format in Excel. Hope that my question will be clarified.

I have many *CSV files and want to copy all those files to 1 Excel workbook (Summary File).

In those *CSV files, they have 1 column 'DATE' and formatted like '04/09/24' (4 Sep 2024).

If I open 1 *CSV file and use mode 'Move or Copy' in Excel to copy it to Summary File, DATE column will be automatically formatted like 4/9/2024 (4 Sep 2024).

I wrote a script in VBA to copy all files automatically (the script below). However, when I run the macro, 'DATE' column will be automatically formatted like 9/4/2024 ( (9 Apr 2024).

I set up my laptop with REGIONAL FORMAT = ENGLISH (SINGAPORE). (the picture below)

There is any way to use MACRO but DATE column will be automatically formatted like 4/9/2024 (4 Sep 2024) the same as I make manually?

Sub copyFiles()

Application.ScreenUpdating = False

Dim folderPath As String

With Application.FileDialog(msoFileDialogFolderPicker)

    If .Show = -1 Then

        folderPath = .SelectedItems(1)

    Else

        Exit Sub

    End If

End With

Dim fileName As String

fileName = Dir(folderPath & "\*.CSV")

Dim merchantSheet As Worksheet

Do While fileName <> ""

    'OPEN FILE

    Workbooks.Open fileName:=folderPath & "\" & fileName

    Set merchantSheet = ActiveWorkbook.Sheets(1)

    'COPY DATA

    merchantSheet.Copy After:=ThisWorkbook.Sheets(1)

    'CLOSE FILE

    Workbooks(fileName).Close    

    'OPEN NEXT FILE

    fileName = Dir()

Loop

Application.ScreenUpdating = True

End Sub

Answer 1

You'll NEVER get a valid date into your file from the CSV using your code or any suggested code.

At first read this article, this is the way to solve this issue, regardless of your system settings.
Power Query - How to import a CSV file that does not match your locale - Microsoft Community

A date in Excel is a number, a "formatted date" is an illusion. Read this article to get a deep dive into Excel and how all that works:
Power Query: How to create a foreign language calendar and pivot table - Microsoft Community

Andreas.

Answer 2

Hi,

[Update-2]

(convert '04/09/24' (4 Sep 2024) to 4/9/2024 (4 Sep 2024)

add the below section of code

in red arrow

(i assume that DATE is in column B)

pic

'

'/////////// START / Update-2

With ActiveSheet

scol = "B" '<<dates in column B

t = 2

Do While .Cells(t, scol) <> ""

Set r = .Cells(t, scol)

v = Split(r, " ")(0)

Z = Right(r, Len(r) - Len(v))

v1 = Split(v, "/")

v11 = v1(0): a = Replace(v11, "'", "")

b = v1(1)

v13 = v1(2): c = Replace(v13, "'", "")

If Val(a) < 10 Then

rep1 = Replace(a, "0", "")

Else

rep1 = a

End If

If Val(b) < 10 Then

rep2 = Replace(b, "0", "")

Else

rep2 = b

End If

s1 = Format(DateSerial(c, b, a), "d/m/yyyy")

r.Value = s1 & " " & Z

t = t + 1

Loop

.UsedRange.EntireColumn.AutoFit

End With

'///////////END

Answer 3

You may add this macro to switch month and day after open your csv file

Workbooks.Open fileName:=folderPath & "\" & fileName

When it is Apr 9, it will change to Sep 4

Sub SwapMonthDay()

Dim ws As Worksheet 

Dim lastRow As Long 

Dim cell As Range 

Dim dateValue As Date 

Dim dayPart As String 

Dim monthPart As String 

Dim yearPart As String 

' Set the worksheet where your DATE column is located 

Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name 

' Find the last row with data in the DATE column 

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 

' Loop through each cell in the DATE column, change the column A to the the data column

For Each cell In ws.Range("A2:A" & lastRow) ' Assuming the DATE column is in column A and starts from row 2 

    If IsDate(cell.Value) Then 

        dateValue = cell.Value 

        dayPart = Format(dateValue, "dd") 

        monthPart = Format(dateValue, "mm") 

        yearPart = Format(dateValue, "yyyy") 

        cell.Value = DateSerial(yearPart, dayPart, monthPart) 

    End If 

Next cell 

End Sub

Answer 4

Hi,

You may try and this approach...

Convert Date (as text string)

to 'Real' Date

custom format: d/m/yyyy (d mmm yyyy)

add the below vba

.

'///////////START

With ActiveSheet

scol = "B"

t = 2

Do While .Cells(t, scol) <> ""

Set r = .Cells(t, scol)

v = Split(r, " ")(0)

v1 = Split(v, "/")

a = Replace(v1(0), "'", "")

b = v1(1)

c = Replace(v1(2), "'", "")

s = DateSerial(c, b, a)

With r

.ClearContents

.NumberFormat = "d/m/yyyy (d ddd yyyy)"

.Value = s

End With

t = t + 1

Loop

.UsedRange.EntireColumn.AutoFit

End With

'///////////END

Answer 5

I found the answer. Thank everyone for your support.

Have a nice day!