Problem with Date Formatting

[JCamara]

It should be simple, but it's driving me crazy.
First of all, i'm brazilian and here standard date is dd/mm/yyyy.
Whenever excel gets the chance it autmatically formats my date as north american dates (mm/dd/yyyy)...

Example: 06/07/2016 turns 07/06/2016...

It happens in every first 12 days of a month, if the day can be a month it'll be changed.

I have dates that come from different origins, and one of them comes in "dd.mm.yyyy" format, the other "dd/mm/yyyy", and later i'll need to compare them... I'm trying to bypass the same problem in find and replace codes, wich besides alterig the first 12 days of a month to mm/dd/yyyy, it some times eats the zeros of months and days (it only doesnt work in code, when i use manual find and replace it's all good)

I tried .NumberFormat = "dd/mm/yyyy" and it still doesn't work.

I even tried splitting it like:

For Each c In Range(Range("C2"), Range("C2").End(xlDown)) 

    f_day = Left(c, 2)
    f_mth = Mid(c, 4, 2)
    f_year = Right(c, 4)
    
    c.Value = f_day & "/" & f_mth & "/" & f_year

Next c

But it also converts to mm/dd/yyyy every first 12 days of a given month.



Now with this one I ALMOST get what I want.

For Each c In Range(Range("C2"), Range("C2").End(xlDown))
    
    c.NumberFormat = "@"
    f_day = Left(c, 2)
    f_mth = Mid(c, 4, 2)
    f_year = Right(c, 4)


    c.Value = f_day & "/" & f_mth & "/" & f_year
    c.Value = Format(c.Value, "dd/mm/yyyy")     'last but one
    c.NumberFormat = "dd/mm/yyyy"                 'last line

Next c

After running it they're all date formats, they're all "on the same side of the cell" (left), and they're all dd/mm/yyyy format...

BUT... if i compare it with a regular date (the one that doesnt come with "."), it won't match, and excel will tell me they're different.... even if they have same format and look the same... maybe it's because i made them text first, but that's the only way i found to make them not get inverted...

how can I overcome this? Is there any settings to make "dd/mm/yyyy" standard date format?


note: if i take this last code (the one that almost works) and switch 'last line' with 'last but one' (see coments), i get to the same point where they're all date formated, but some (first 12 days) are mm/dd/yyyy and aligned with right border of cell, while others (the rest of dates) are dd/mm/yyyy and aligned with the left.

 

[SpillerBD]

The available defaults should be defined from the "Regions and Language" settings in the Control Panel for your computer.
Unfortunately the desired format may not be available and you may have to adapt in a different way.

 

[Norie]

Where are the dates coming from and how are you getting them into Excel?

 

[Jonmo1]

Try

c.Value = DateSerial(f_year, f_mth, f_day)

 

[JCamara]

SpillerBD , I already looked up there, and it was set for Brazilian Date Standard... Anyway, thanks...

Norie the dates are comming from different SAP transactions via CTRL+C, CTRL+V.

Jonmo1 OH MY GOD, MAN YOU ARE THE BEST! <3 Apparently it worked, i made a simple comparison between results with "=cellX=cellY" and it came as TRUE...

 

[Jonmo1]

You're welcome.

The root issue is basically that VBA is US centric, regardless of your PC regional settings.
So when it sees 07/11/2016, VBA interpraits that as July 11th, not November 7th.
But if it sees 15/11/2016, it's forced to realize that is November 15th because there is no such month as 15.

Using Dateserial eliminates that ambiguity when the day is 12 or less.