Date problem - European/American formatting

[bdn435]

Hi,

After copying a range of records (containing a.o. dates) to another (=destination) sheet, the date format, originally in dd/mm/yyyy format, changes to mm/dd/yyyyy for those dates with an original dd < 13. This means that 07/01/2014 (dd/mm/yyyy) becomes 01/07/2014 in the destination sheet.
The regional settings on my computer at set to 'British' being dd/mm/yyyy.

Any thoughts what I've been overlooking while copying/storing?

Thanks for your help
Eric

Just to be more specific on the copying: I've move the 'source' records to an array and then drop the array in the destination sheet.
Do I need to format the date in the array as a string before dropping the array ?

[isskint]

The age old issue with dates

If you have access to the original data still, add 3 extra columns for Day(), Month() and Year(). Copy the data over and rebuild the dates using the Date() function on the 3 extra columns.

[bdn435]

Thanks! Seems an inevitable 'workaround' but a good solution.

[Norie]

Are the date values changing or is it just the formatting?

Also, how are you copying/pasting?

[bdn435]

The value are changing.

I'm building a (variant) array with the records and then drop the array into a range in the destination workbook.

[Norie]

Eric

What format are the dates in the array, before you drop them in the range?

[bdn435]

Hi Norie,

Thanks for taking such an interest in my problem.
Just debug.printed the array and the dates are in the dd/mm/yyyy format (as in the source sheet)

[Norie]

I can't recreate the problem, do you have 'real' dates on the source sheet?

In the array are the values enclosed in #s?

PS For that last bit you'll need to look in the Watch Window.

[bdn435]

Hi Norie & Co,

Seems that the following solution works

- Store the date field from the source sheet in the (2D) array with a .value2 property - in english: MyVariant(a,b) = MyCell.Value2 . This stores the date as a dateserial
- Format the range with the dateserials in the destination sheet as dates - in english MyRange.NumberFormat = "dd-mm-yyyy"

The inspiration for this solution comes from the following tread:
http://www.mrexcel.com/forum/excel-q...ray-range.html

and see also
http://stackoverflow.com/questions/1...lue-and-value2

and also
http://www.excelforum.com/excel-prog...nd-value2.html

Regards
Eric

[Norie]

Oops, misread - thought it was a different post.