Date formatting
Results 1 to 9 of 9

Thread: Date formatting
Thanks Thanks: 0 Likes Likes: 0

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

    Default Date formatting

    I am trying to have a user type for example: 1/2/3 meaning 1st Feb 2003, and have Excel automatically convert it to 01/02/2003. However when I select the custom date option: dd/mm/yyyy in the format cells window, in the formula bar is 02/01/2003, the American format.
    How can I get both displayed dates to read 01/02/2003 or alternatively 01/02/03.


    Thanks,
    Richard

  2. #2
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date formatting

    Hi,
    Choose custom format and type in mm/dd/yy or mm/dd/yyyy
    "Fair Winds and Following Seas"

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

    Default Re: Date formatting

    Unfortunately that now displays 02/01/03 in the cell, and 01/02/03 in the formula bar.
    I require both of the values to be the same, 01/02/03.
    Is there anyway of changing the international date settings, as I suspect that on my PC it is set to US format.


    Thanks,
    Richard

  4. #4
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date formatting

    Windows control panel, regional settings, date. Which will also affect other windows pgms.
    "Fair Winds and Following Seas"

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

    Default Re: Date formatting

    Thanks for the help,

    Unfortunately even though the settings are all UK, and the date format is set as dd/mm/yyyy, Excel still converts the date into mm/dd/yyyy format.

    I am confused. Has anyone got a suggestion for what the problem may be?
    It is important that both the display and the formula bar show the date in the correct format, as I am analysing the data and must have UK date format.


    Thanks,
    Richard

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

    Default Re: Date formatting

    I should say that the date when I enter eg. 01/02/03 trying for 1st Feb 03, Excel understands this as 2nd Jan 03.
    Even if I set the format as mm/dd/yy, Excel still has this as 2nd Jan 03. I need it to be actually 1st Feb 03, despite what is displayed.
    My regional settings are set to UK settings, ie. should display 1st Feb 03.

    Do you know what is wrong?


    Thanks,
    Richard

  7. #7
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date formatting

    I cannot explain that. If I change the "Short Date" format where I describe above, that instantly applies to how the dates are shown in the formula bar.
    "Fair Winds and Following Seas"

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

    Default Re: Date formatting

    Its weird. It was working for me not long ago as well. I don't know what has happened.

    See also http://www.mrexcel.com/board2/viewtopic.php?p=289230


    Thanks for your help,
    Richard

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

    Default Re: Date formatting

    I have identified my problem. The date format was following the UK settings until I pasted the following code into the workbook, designed to force the contents of the workbook to be capitalised.
    Can something be added to force the date format to stay as the UK settings, not switch to US.


    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




    Thanks,
    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
  •