Displaying date in correct International format
Results 1 to 6 of 6

Thread: Displaying date in correct International format
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Displaying date in correct International format

    I am using Excel 2002 - I have received a worksheet with various dates keyed in by a colleague in USA. For example one of the dates is shown as "12/10/02" which should be viewed at "December 10th, 2002". However when I format the date cell to view the full month it shows as "October 12th, 2002".
    Can anyone tell me a way to make the full date format show as its original intented date?

    Barrie Bowdler

  2. #2
    New Member
    Join Date
    Sep 2002
    Location
    London - ish
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I had a similar problem dealing with the US and came to the conclusion that the Regional Settings dictate how the dates are shown, however this does mean that if you enter a date in the UK the date is "converted" over to US format when opened there and visa versa, so the date you refer to should have represented October 12th and not Dec 10th

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Displaying date in correct International format

    If you format the date as General what does it show. 12th October 2002 is 37541, 10th December 2002 is 37600. Dates are only numbers, so there can be no ambiguity - except in VBA which does have problems with US/UK dates.

  4. #4
    Board Regular
    Join Date
    Jul 2003
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displaying date in correct International format

    I have the same problem as gjadcock. I have entered 10/12/02, which converts to 37600, ie. the US format. This happens even though I have set the regional settings to UK.
    Has anyone got any suggestions for solving this strange problem?


    Thanks,
    Richard

  5. #5
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Soliohull
    Posts
    1,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displaying date in correct International format

    If you have entered 10/12/02 in your cell and it converts (when viewed as general) to 37600 then Excel is interpreting the date as dd/mm/yy (i.e. 10 Dec 2002), which is UK format.

    Did you want it to be 12 October 2002 ?

  6. #6
    Board Regular
    Join Date
    Jul 2003
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displaying date in correct International format

    Sorry I am getting myself confused. I entered 12/10/02, and it converted to 37600.
    I have identified what has caused the problem though, but don't know how to solve it. I added the following code to capitalise the data in my workbook, and removing it leads to 12/10/02 becoming 37541, ie. UK format. How can I continue to have this format, and use the following code:


    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As

    Excel.Range)
    Dim c As Range

    Application.EnableEvents = False
    For Each c In Target.Cells
    c = UCase(c.Text)
    Next
    Application.EnableEvents = True
    End Sub


    If possible is there a date format function I can add to the code?


    Thanks a lot,
    Richard

Some videos you may like

User Tag List

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
  •