Force European Dates Format

pipoliveira

New Member
Joined
Feb 15, 2013
Messages
12
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 1st of August 2019 instead of 8th 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:
Code:
‘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. “1st March 2019”.
Any ideas will be highly appreciated.
Best regards,
Filipe
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,186
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,791
Messages
5,524,900
Members
409,610
Latest member
db321

This Week's Hot Topics

Top