DATE reversed when run Paste Special by VBA code

[Hyakkivn]

Hi everyone !
As the title, I got problem with paste special function when I copied data (table type) from 3rd party software (wrote in VB.net language with DATA store to an Access file) to excel.
Everything went well until it copied date. For example, the date is: 08 Oct 2021 (08/10/2021), after copy and paste special by macro, it become 10 August 2021 (10/08/2021)
But if I do it manually (Ctrl+C at software then right click and choose Paste Special on Excel), nothing wrong with that date.
Any help will be appreciated.
Best regard !!!

 

[jasonb75]

VBA defaults all dates to US (mm/dd/yy) format, I've had problems with this before when I had to manipulate dates that are in UK (dd/mm/yy) format in vba but would have thought that copy and paste should work correctly.

If it's happening to all dates then I personally would use text to columns after parse the dates back to the correct format (integrated into the copy and paste code). Others may have different ways to fix it.

 

[JEC]

Maybe set the numberformat to "dd/mm/yyyy" first.

For example

Sub jec()
Range("A2").NumberFormat = "dd/mm/yyyy"
Range("B2").Copy
Range("A2").PasteSpecial xlPasteValues
End Sub

 

[jasonb75]

Maybe set the numberformat to "dd/mm/yyyy" first.

That is a bad way to do it. Typically what will happen is that the month and day will be switched if the day is 12 or less but will still be a valid numeric date. Dates with a day of 13 or more will remain as original but will be in text format.

Text to columns can fix all dates at once when this happens but messing with cell formats before pasting can prevent this from working correctly.

 

[JEC]

Then text to columns it is

 

[Hyakkivn]

Maybe set the numberformat to "dd/mm/yyyy" first.

For example

Sub jec()
Range("A2").NumberFormat = "dd/mm/yyyy"
Range("B2").Copy
Range("A2").PasteSpecial xlPasteValues
End Sub

I thought and tried this before. No help. The date still be reversed. Why don't we use just one date format only..brrrr. Let me try Jason's solution.

 

[jasonb75]

I thought and tried this before. No help. The date still be reversed.

If you've already changed the format then text to columns may not work correctly. It would be best to clear the data and paste to cells with a 'General' format, then apply text to columns, selecting MDY from the date dropdown on the last tab.

 

[Akuini]

I've answered a similar question in this thread:

Mixed Date Formats in the same Column

I have a big spreadsheet, some 750000 rows. In Column C I have dates. Approximately half of the dates in this Column are in dd/mm/yyyy format, the other half in mm/dd/yyyy format, I think. eg. 31/05/2012 then 01/06/2012 (appears as 06/01/2012 in the formula window) Any help to get them ALL...

www.mrexcel.com

see it helps.

 

[Hyakkivn]

I've answered a similar question in this thread:

Mixed Date Formats in the same Column

I have a big spreadsheet, some 750000 rows. In Column C I have dates. Approximately half of the dates in this Column are in dd/mm/yyyy format, the other half in mm/dd/yyyy format, I think. eg. 31/05/2012 then 01/06/2012 (appears as 06/01/2012 in the formula window) Any help to get them ALL...

www.mrexcel.com

see it helps.

I 've tried your code, but it do nothing. I think my problem is different from others. I copy data from 3rd party software, then paste to excel. And when "the macro copy and paste special date", it swaps day to month - month to day. All I can do is do copy paste special manually. ?

 

[Akuini]

I copy data from 3rd party software, then paste to excel.

Could post some sample of dates after you paste to excel?