excel date format conflictng between US and non-US

[fanof]

Hi all,

First message on that forum, so glad to join the community.

I have an issue with date formats.

I have a CSV file with dates in the US format: MM/DD/YYYY
I am French, we are using the date format DD/MM/YYYY (called here: non-US format).


My question is NOT about converting one into the other. I can do it, it is not very difficult. I have a different issue.

I made a vba program that copy-paste those dates from one sheet to another to later process them to do whatever I need to do.
The problem I have is that sometimes excel understands it is originally a US format and automtically converts it into non-US when pasting. But sometimes it does not understand it and does not convert it...
The weirdest thing about it is that the same file, wih the same dates and processed in the same way may give different results, sometimes excel understands, sometimes it doesn't.... It seems to happen randomly.

Because of it, there is nothing I can reliably do to automate the format change. Any code I write might work if dates remained in US format when pasting, but will fail if it had been converted, and vice versa.

What I would like is to either:
-inform excel that those dates are US format in the first place instead of letting it decide. (I don't even know if it is possible)
-disable auto-convert when pasting.
- or, be able to know what format Excel believes it is, so I can automatically decide whether I trigger my conversion program or not.

Can someone help me with this?

Thanks.

 

[Kerryx]

Found this explanation ,maybe it can help

With dates, if your OS (Windows) is in English-US you can use r.Value = t

Set r = Range("a1:a9")
t = r.Value
r.NumberFormat = "@"
r.Value = t

Otherwise if you do not want the dates to be converted to the US date format, you must use r.FormulaLocal = t

Set r = Range("a1:a9")
t = r.Value
r.NumberFormat = "@"
r.FormulaLocal = t

When a data is sent from vba to a cell, an internal type conversion function is called if required, that is if the data type is different from the numberformat property. This internal type conversion function recognizes only the international date format (yyyy / mm / dd) and US dates formats (eg. mm / dd / yyyy)

You can use cell.FormulaLocal = data in place of cell.Value = data to avoid this function to be called.

 

[Alex Blakenburg]

The problem I have is that sometimes excel understands it is originally a US format and automtically converts it into non-US when pasting. But sometimes it does not understand it and does not convert it...

Typically it will convert it if the what it thinks to be the month position is 12 or less and it will keep everything that has > 12 in the month position as a Text value instead of a date value. The conversion is generally incorrect in that it will have reversed the day & month.
You will be better off showing us what you are using as a source and show us what outcome you would like.