Date formats being switched between UK / US formats when possible...

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,227
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hey All

Weird one here, I've had this before but I've always been able to bodge my way around it somehow, but this time I can't and it's finally time to try and solve the problem once and for all.

I'm from the UK - When I'm using VBA to copy and paste a worksheet from a workbook on our company network drive to a workbook on my local desktop that has dates in it, the dates that can be changed to US format are being.

So, for example;

26/06/2018
26/06/2018
11/06/2018
11/06/2018
25/06/2018
25/06/2018


<colgroup><col></colgroup><tbody>
</tbody>
Becomes...

26/06/2018
26/06/2018
06/11/2018
06/11/2018
25/06/2018
25/06/2018

<colgroup><col></colgroup><tbody>
</tbody>

What was the 11th June 2018 becomes 6th November 2018, the other dates that can't be swapped around aren't.

It's not a formatting issue I don't think as the value is being changed from 43262 (11/06) to 43410 (06/11)

I've checked the "Locale" information in both Excel and Control Panel, both are set correctly to English (United Kingdom)

If I just open the Workbook on the network drive and copy and paste the sheet into a new workbook manually, the dates remain correct.

It's not just these dates that are being swapped around, any time the day / month can be swapped and made into a US format date it is being doing 11/12 becomes 12/11 and 06/07 becomes 07/06 etc.

There is nothing fancy about the code I'm using to copy and paste the worksheet;

Code:
    Workbooks.Open Filename:="Z:\Pricing Sheets\Pricing Analyser - " & FName & ".xlsm", ReadOnly:=True, UpdateLinks:=xlUpdateLinksAlways
    ActiveSheet.AutoFilterMode = False
    Cells.EntireColumn.Hidden = False
    Cells.Copy
    ActiveWorkbook.Close savechanges:=False
    ActiveSheet.Paste

(Fname is just the variable part of the filename defined by a Cell earlier in the code)

This makes no sense to me :(

Anyone come across anything like this before?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi Jazz,

Came across this issue last year and last month. I couldn't find any specific reasons for it, nor any code or Excel related fixes.

One suggestion that worked for me

Close all instances of Excel
Change system date settings to anything other than UK
Apply
Switch back to UK
Apply
Reopen Excel

Prior to this, I'd written separate code that read dates into their Year, Month and Day components and rebuilt them; it helped the data could easily identify which dates needed correcting or trying to decide whether 01/03 or 03/01 was problematic!

Reminded me of the standard IT response to Windows problems "Have you tried restarting the PC?" I think resetting system the date resolves in a similar way.
 
Last edited:
Upvote 0
Bah - No, that didn't work for me :(

Tried variations on the theme (Change / Apply / Restart Machine etc.) but nothing I tried worked.

Actually pretty gutted as that would have been perfect, I'm a big fan of turning it off and and on again solutions.

And, yes - Same here, my "bodged" solutions probably rely on similar code - I can't use that this time though because asides from the sheer volume of dates being dealt with, its important that the dates are right for any future events that take place.

Frustrating that they won't all just switch to the one format and have done, least I could work with that easier :-/
 
Upvote 0
Are you sure that the dates aren't stored as text in the source workbook? That would be the only 'non-bug' explanation I can think of currently.
 
Upvote 0
Are you sure that the dates aren't stored as text in the source workbook? That would be the only 'non-bug' explanation I can think of currently.

Unfortunately, I am sure :(

If I use VALUE it returns the serial number of the date, and a quick check I just did with ISTEXT returns false - I get the same results from the pasted data.
 
Upvote 0
Perhaps a clipboard issue then. Does it help if you use something like this:

Code:
Dim ws as worksheet
set ws = activesheet
 Workbooks.Open Filename:="Z:\Pricing Sheets\Pricing Analyser - " & FName & ".xlsm", ReadOnly:=True, UpdateLinks:=xlUpdateLinksAlways
    ActiveSheet.AutoFilterMode = False
    Cells.EntireColumn.Hidden = False
    Cells.Copy ws.range("A1")
    ActiveWorkbook.Close savechanges:=False
 
Upvote 0
I've always found the copy destination & pastespecial are more reliable than paste.
try something like
Code:
   Dim ws As Worksheet
   Set ws = ActiveSheet
   Workbooks.Open Filename:="Z:\Pricing Sheets\Pricing Analyser - " & Fname & ".xlsm", ReadOnly:=True, UpdateLinks:=xlUpdateLinksAlways
   ActiveSheet.AutoFilterMode = False
   Cells.EntireColumn.Hidden = False
   Cells.Copy ws.Range("A1")
   ActiveWorkbook.Close savechanges:=False
 
Upvote 0
Hi @Fluff, I maybe missing something here but your code is almost same as Rory's [at least for Cells.Copy ws.Range("A1") ]
Is there a difference in this and what you mean by copy destination & pastespecial?
 
Upvote 0
I suspect we were writing it at the same time, I just hit submit sooner. ;)
 
Upvote 0
I hadn't seen Rorry's code when I posted & yes they are the same.
The code Rory & I posted is using Copy Destination, which bypasses the clipboard. The Paste special would be like
Code:
   Cells.Copy
   ws.Range("A1").PasteSpecial [COLOR=#0000ff]xlPasteValues[/COLOR]
With the part in blue it will paste values only, without it will paste formulae & formats
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top