+ Reply to Thread
Results 1 to 8 of 8

Date Formatting

  1. #1
    Registered User
    Join Date
    10-18-2007
    Posts
    2

    Date Formatting

    I have received a spreadsheet at work and the date column has been filled in as text in the format "4th November 2007" for over 400 entries.

    Is their any way to convert this to a date format "DD/MM/YYYY"?

    Many thanks for your help in advance.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello PSFM_Richard,

    What does the text in the Formula Bar look like when you select one these cells?

    Thanks,
    Leith Ross

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try

    Select Column > Press ctrl + H > Find enter th and replace leave blank

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    .....presumably you might also need to do a Find/Replace on "nd", "rd" and "st"
    Last edited by daddylonglegs; 10-18-2007 at 03:51 PM.

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Unless you do Find ust and replace with blank

    VBA Noob

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Sorry VBA Noob,

    I edited my post because when I re-tested I noticed that replacing "st" in 1st August 2007 gave 1 Augu 2007 and that still succesfully converted to a date [to my surprise]

  7. #7
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    No probs

    Thanks for testing thou and good spot

    VBA Noob

  8. #8
    Registered User
    Join Date
    10-18-2007
    Posts
    2

    Thanks

    Thanks for your help guys - you've been very helpful.

    I'm off to do some finding and replacing!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1