Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a number of dates formatted as yyyy/mm/dd which I would like to convert to long date dd month yyyy. My Control Panel regional settings are set to English UK.

    Could anybody advise me, please.
    I am using Windows XP and Office XP.

    Thanks,

    SteveS

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    What you need is a Custom Date.

    On Menu Bar select Format Cells. In the Dialog Box under Categories select Custom.

    Put the following in the box mm mmmm yyyy

    this should yeild 12 March 2010 (if the cell you are on has todays date)

    If you want dashes mm-mmmm-yyyy for slashes substitue "-" with "/"



    Regards,

    Tom D

  3. #3
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Tom, but that seems to leave the date exactly as it was, ie, 1804/05/20.

    SteveS

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Are your dates all prior to 1/1/1900? Excel doesn't work with dates in cells prior to 1/1/1900, though VB and VBA will. See http://j-walk.com/ss/excel/usertips/tip028.htm
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John,

    Thanks for that; I will try out your add-ins. I knew there was something about pre-1900 dates, but had forgotten.

    Tom D

    You are right, when the date is post-1900 it works fine. Sorry for doubting you.

    SteveS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    It's John Walkenbach's add-in, not mine; his site and books are an excellent resource for us Excel-mates.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Lounger
    Join Date
    Aug 2004
    Location
    Crowborough, Sussex, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    JohnBF,

    I have tried out XDATE, and it works beautifully, thanks. To get over the very early date problem, I have split the original into three cells, using the RIGHT, LEFT and MID functions, then applied your XDATE function

    I'm very grateful, it has saved me a great deal of work.

    SteveS

Posting Permissions

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