Force European Dates Format

[pipoliveira]

Dear all,
Trust you’re all well.
I have a dashboard with VBA forms that uploads csv files taken from our internal system to a specific dashboard.
All info is being pasted on the right columns and so on, the only field giving me headaches is the one is being extracted with date and time even though, on the CSV file, the cells does not recognize the info as date. They came on the following format: “dd.mm.yyyy hh:mm:ss” as attached example in column “H”.
The issue I have is with some colleagues based in the US. When I copy this info from CSV file into Excel as date, some of the dates comes in US format and some dates in European.
E.g., if one of the dates are 08.01.2019, it will reverse to 1^st of August 2019 instead of 8^th of January 2019.
As we do not need the hours; seconds and milliseconds, I have created a code to delete it and only paste the date.
The code I use to copy from CSV file and paste to XLSM file is the following:

‘ScWs = Source File (CSV)
‘TgWs = Target File (xlsm)

TgWs.Cells(iRowReg, 6).Resize(98, 1).NumberFormat = "@"

ScWs.Range("H2:H100").Copy

TgWs.Cells(iRowReg, 6).Resize(98, 1).PasteSpecial xlPasteValues

'Code to keep only date and delete the hours "00:00:00"
For Each Cell In Range("F14:G1000")
    If Not IsEmpty(Cell) Then
        Cell.Value = Left(Cell, 10)
    End If
Next Cell

On first line of code, I have forced to format the column target as “text” to avoid excel mix the dates.
My goal is to paste the dates and force the European format such as e.g. “1^st March 2019”.
Any ideas will be highly appreciated.
Best regards,
Filipe

 

[Fluff]

Cross posted https://www.excelforum.com/excel-programming-vba-macros/1267439-force-european-dates-format.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[pipoliveira]

Dear All,

This same thread was also posted on the ExcelForum site:

https://www.excelforum.com/excel-pr...-force-european-dates-format.html#post5078676

As per the rules and request from admin team.

Any assistance will be highly appreciated.

Best regards,
Filipe