CODEWITHSUNDEEP
vbaexceldate
Nacorid
Nacorid

Reputation: 793

How do I copy dates from a different regional format in Excel VBA?

I have a workbook I use to collect relevant data from a different workbook I only have read-rights to. I connected the latter to my own with Excel's built-in function "Data-connection". Copying a worksheet like this and then copying relevant data from the copied sheet is in my case (yes, I tested it) faster (by about 30%) as opposed to opening said workbook and then copying the relevant data directly from source.

The default date format in the source is "dd/mm/yy" (though it obviously adapts to the local date format settings when opening). The copied sheet keeps that date format but when copying the dates with regional settings as "english-US" Excel assumes the source format to be "yy/mm/dd" even though the cells are formatted as "dd/mm/yy".

And here lies the problem: Not all users of my workbook can use the local date format of "dd/mm/yy", some have to use "mm/dd/yy" or "yy/mm/dd".

How can I tell Excel to copy from a specific date format to the local date format when the local format is not always different?

Checking for the local format with Application.International(xlDateOrder)and then changing the dates if the format differs seems like a complicated workaround which I would like to avoid.

The source format is dd/mm/yy after copying with TargetSheet.Cells(TargetRow, TargetColumn).Value = CDate(SourceSheet.Cells(SourceRow, SourceColumn).Value) the format was dd/mm/yy with days as year and year as days.

Upvotes: 1

Views: 607

Answers (1)

Vegard
Vegard

Reputation: 4927

It might depend on how exactly you are copying the data.

In your case it isn't entirely clear to me what your source data looks like, but for instances where typecasting dates with CDate isn't sufficient to make Excel infer the correct format by itself, you should be able to force the correct interpretation using Format:

TargetSheet.Range("A1").Value = _ 
    CDate(Format(SourceSheet.Range("A1").Value, "dd/mm/yy"))

Do note that you might encounter datasets where you'll need to tweak this method (for example data with varying formats or particular formats that are ill suited for Excel's algorithm for understanding the conversion).

In some instances (text conversion maybe), you may have to apply the format afterwards. That is, applying Format to a CDate object instead of the other way around. I haven't looked this up in any significant extent and can't provide specific details or examples, but I am mentioning it so that you'll be on the look-out.

I am also unsure to what extent this approach will work for source formats that are outside the "english-US" format that is standard in VBA.

Upvotes: 2

Related Questions