Force European Dates Format

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:

Please Login or Register  to view this content.

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

Administrative Note:
Welcome to the forum
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums.
Read this to understand why we (and other sites like us) consider this to be important.

(note: this request is not optional No Help to be offered until the link is provided)

Hi All,

As per Admin request and as per the rules, I have also created this same thread on other Excel forum with the following link:

https://www.mrexcel.com/forum/excel-...es-format.html

Any assistance will be highly appreciated.

Best regards,
Filipe

Hi Pipoliveira,

In your code you leave the identification of dates to the interpretation of excel in the For Each Cell In Range("F14:G1000") loop. This can cause problems if the date separator (in your export.csv it is point) is not recognized by the local settings then dates will often stay text. f.e. on my system dates are separeted with minus so 08-03-2019 for today.
Another common problem is (as you have noticed) that if excel does recognize a date but the day is under between 1 and 12 then days can get swithed around if you only shorten the string to the date part. Left(cell,10) in your code

The only way to be sure a date will be handled correct you will need to a proper conversion to datevalue in VBA yourselfyou can do that with the command dateserial. In your export file the date is always dd.mm.yyyy so that will be easy to put into a vba.

I have created below code for you to test on your export.csv as I dont have the other file with range F you can use this code to test on export.csv and then later change the ranges in your own macro

Please Login or Register  to view this content.

dateparts is an small array that will split the first 10 characters of the date time text string into 3 parts based on pointseparator so rsulting in
day=dateparts(0) month=dateparts(1) and year=dateparts(2)
then the nextline replaces the current datestring with a datevalue. With cellformatting you can then control how to show the date.
For use in international environments I always recommend to use a format with short monthname (Jan, Feb) instead of numeric months because that eliminates a lot of confusion of day and month. If a USA employee sees 08.Mar.2019 he might get annoyed that it is the wrong way around but he wont confuse the date for august 3rd

so it is a bit of a mix of handling dates in VBA and choosing smart formatting for international use to eliminate confusion.

Is this what you want at the press of a button.

Capture.JPG

Hi Torachan,

Thanks for your replies.

Your code seems to work ok but for that, you have merged (concatenated) all columns into one.

I want to avoid that as I am looking for only copy the columns from the export file as export it properly as dates only in European format.

For the US colleagues, they can after change it manually if they pleased.

Thanks a million,
Filipe

Hi Roel,

Thanks a million for your reply and code.

First, I have to take out the "Option Explicit" as the "dt" Dimension was not set.

But even though, the code gave me an "Subscript out of range" on the following line:

Please Login or Register  to view this content.

Not sure how to debunk it.

Thanks a million,
Filipe

Hey Roel,

I've debunked a little your code and solved the out of range issue with error handler.

The only thing I had to do is to format the cells into the date format desired ("d mmm yyyy").

The final code works great as desired (little change from yours as I add this one to the Dashboard and not the sample):

Please Login or Register  to view this content.

Thanks a million for this piece of code and explanation.

I am really appreciated

Best regards,
Filipe

Hi Torachan,

No, I did not wanted to merge all columns into one but only to force excel to accept the dates that were exported from the system (csv file) to european ones.

The code from Roel converted the info copied from CSV file to the required date format.

Best regards,
Filipe

Hi Roel,

Trust you're well.

I had to reopen this thread as I am having issues with my US colleagues that have on their system settings the US date format.

Attached is the export file with the dates in US format.

I have used your code and even I have tried to swap the DateSerial with no avail.

Most of the values get recognized for US dates but, for example, cell H2 gives the date of 1st of December instead of 12th of January; Cell H27 gives 7th of February instead of 2nd of July.

I will have to create 2 CommandButtons separately for European format and US format.

My question is how to twist/change your code as to facilitate the US colleagues.

Your assistance on this matter will be highly appreciated.

Best regards,
Filipe