Date Day and Month Reverse When Copying Data from a Range To A Table using an Array

[TheScott]

I have a problem where I pass a range, of which one cell is a date (formated DD/MM/YYYY) to an array . The array then pastes to a table row. The date however has the DAY and MONTH transposed.

Any insight as to why this is happening, I would appriciated it.

ReverseDateExp.xlsm

Regards
Scott

[TMS]

It's because the worksheet uses Regional Settings for dates but VBA is American through and through.

Given the nature of the specific example, I'd just go with:

Please Login or Register  to view this content.

Regards, TMS

[bulina2k]

The F column is formatted as Custom (mmm.yyyy). Select the whole column and format it as short date.

[TheScott]

TMS Humm….. OK, I understand what you are saying but something is lost in my understanding of Excel and how it processes dates. It has been my understanding that if you were to format a cell as dd/mm/yyyy and another as mm/dd/yyyy and entered the same date(following the appropriate format), the underlying number that Excel uses to process dates would be the same…. Is this not true?

I appreciate the fix to this application but I am not sure I can use it elsewhere without a better understanding of dates and arrays and what happens to them when they cross that excel / vba threshold. Any insight would be appricated.

Bulina2k - I attemped to format the entire F column... and just the cells within the table in a variety of ways. I used the custom MMM/YY because it was just easier to see the date problems for this example. no matter what format is used in the f column if you put a date in B4 (which is formatted dd/mm/yyy) which starts with any number over 12... you get a text entery in column F.

Regards
Scott

[TMS]

I think that, be default, you are copying the date as shown in the cell, that is the Regional, as displayed, date. That is then processed in VBA and, when pushed back out, you're getting the American version.

Try using Value2 instead of defaulting to Value

Please Login or Register  to view this content.

As far as I understand, Value2 will give you the underlying numeric value and, consequently, it will not get massaged in the transition.


Regards, TMS

[TheScott]

Thumbs Up! Learned something new and that can't be bad. I appreciate your help!

As an added thought for others who might stumble on this thread. I had thought that using the .Value2 when applied to the entire array might cause errors with cells containing underlying formulas but as far as I can tell, it does not and will paste the value obtained by the calculations....

Regards and Thanks

Scott

[TMS]

A little more information ... https://fastexcel.wordpress.com/2011...w-to-avoid-it/

Range.Value2

This works the same way as Range.Value, except that it does not check the cell format and convert to Date or Currency.

And, just for fun:

Please Login or Register  to view this content.

Regards, TMS