Page 1 of 3 123 LastLast
Results 1 to 10 of 22

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

  1. #1
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,168
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

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

    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
    Becomes...

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

    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?
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,072
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Date formats being switched between UK / US formats when possible...

    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 by JackDanIce; Jun 26th, 2018 at 07:00 AM.


  3. #3
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,168
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Date formats being switched between UK / US formats when possible...

    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 :-/
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,500
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Date formats being switched between UK / US formats when possible...

    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.

  5. #5
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,168
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Date formats being switched between UK / US formats when possible...

    Quote Originally Posted by RoryA View Post
    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.
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,500
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Date formats being switched between UK / US formats when possible...

    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

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,203
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Date formats being switched between UK / US formats when possible...

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,072
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Date formats being switched between UK / US formats when possible...

    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?


  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,500
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Date formats being switched between UK / US formats when possible...

    I suspect we were writing it at the same time, I just hit submit sooner.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,203
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Date formats being switched between UK / US formats when possible...

    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 xlPasteValues
    With the part in blue it will paste values only, without it will paste formulae & formats
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •