Date changes went being pasted

If I remember correctly, csv files assume the use of American date settings (mm/dd/yyyy) by default. So what's probably happening is that when the date can be an American date (for example 05/08/2018) then it will be assumed to be that date (in that example, 8th May 2018, even if that date was meant to be 5th August 2018). If the date cannot be an American date (for example 19/08/2018) then it will be assumed to be the non-American date.

When saving a file with international dates into csv, it's possible to force csv to use international, not American, settings, by using Local:=True. For example:

Please Login or Register  to view this content.

The bad news, however, is that I don't know how to insert this into your code - or if it would even work - my VBA skills just aren't that good. But maybe it'll give you, or someone else on here, a starting point?
Good luck.

Aardigspook is indeed correct. When Excel imports from a CSV if it can be an American date it formats it as such, and if it cannot then it formats it as text. If you resize the column to be wider than necessary and remove any left/right/centre justification you'll see the American dates aligned to the right and the others to the left.

I had exactly this issue at work just before Xmas. My solution was to have the VBA loop through the affected column, row by row, and if it contained a date it would switch the day and month around in a helper column and if not it would use DATEVALUE to convert the text date into an actual date in the helper column. When done, all the dates are in DD/MM format and the helper column can be copied and stamped back over the original column.

At least that's how I think I did it. I don't have access to the file right now.

Very interested to hear other solutions on this! :D

BSB

This formula should work in a helper column.

Formula:

Please Login or Register  to view this content.

If A2 is being read by Excel as a date, it will switch the day and month (for example 11/05/19 will become 05/11/19).
If the 'date' is text, it will extract the relevant parts of the text and make them a date.
It assumes that all the dates, whether being read as dates or text, are in the American month/day/year format.
It also assumes that the text-dates have 4-digit years like 9/28/2019 (because that's how a csv-import presented my test file dates to me). If you're getting 2-digit years in your text-dates (e.g. 9/28/19) then change the red 4 to a 2.

If the formula doesn't work for you, get back to us with what incorrect results you're getting.

Also, I'm afraid I can't help with doing this in VBA - it's not my strong point.

Hope that helps a bit.