+ Reply to Thread
Results 1 to 6 of 6

Multiple Date and Text Formats In One Column

  1. #1
    Forum Contributor
    Join Date
    04-06-2006
    Posts
    131

    Question Multiple Date and Text Formats In One Column

    Hey Everyone!!

    I have a bit of a conundrum. Once a week I have to do some data scrubbing on a file I get from my HR Team. The first step of this is to sort the list by the date column, W, so that the newest records are at the top. The problem is, there is zero consistency in this column. There are only dates in about half the rows, which is fine, but for the other half - there is zero consistency to how the date has been entered. Some of the cells are even text values. Which means, sorting the Column does not work...

    Is there an easy(ish) way of converting all these values to a consistent date format that I could then sort as normal?

    The attached document is a full example of what I am working with - about 81,000 rows.

    Thanx a million for your help!!!
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Multiple Date and Text Formats In One Column

    select column data/text to columns /delimited/next/next/choose date select mdy click finish
    however if someone has put 6 july 2000 as 6/7/2000 as text this will resolve to 7 june 2000
    Last edited by martindwilson; 06-25-2014 at 05:17 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple Date and Text Formats In One Column

    Hi,

    It wasn't actually necessary to show us all 80000 rows

    The simplest way would be to use string slicing to extract the day/month/year elements of the text strings and from them create a proper date. Preface the formula with an IF test so that you keep dates that are proper dates as they are.

    So for example in say X2 copied down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Now just copy column X and paste special values back to W2
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple Date and Text Formats In One Column

    Quote Originally Posted by martindwilson View Post
    select column data/text to columns /delimited/next/next/choose date select mdy click finish
    however if someone has put 6 july 2000 as 6/7/2000 as text this will resolve to 7 june 2000
    Much better Martin. I missed the obvious.

  5. #5
    Forum Contributor
    Join Date
    04-06-2006
    Posts
    131

    Re: Multiple Date and Text Formats In One Column

    Sorry about the full file.. Was just trying to be thorough... Guess that was overboard...

    Martin's solution works great. Like you said, there are going to be a few where the month and day are getting swapped, but I'm going to have to live with that - this still gets me a million miles closer to the finish line than I was.

    Thanks a ton - I really appreciate it!!!

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple Date and Text Formats In One Column

    Quote Originally Posted by nevi View Post
    Sorry about the full file.. Was just trying to be thorough... Guess that was overboard...

    Thanks a ton - I really appreciate it!!!
    No problem and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Various date formats in same column
    By mardiefe in forum Excel General
    Replies: 5
    Last Post: 06-22-2014, 09:42 AM
  2. Replies: 4
    Last Post: 08-25-2012, 07:49 AM
  3. Multiple date formats within a single column
    By the big frame in forum Excel General
    Replies: 3
    Last Post: 02-22-2012, 05:02 PM
  4. How to change multiple date formats in same column
    By muchado in forum Excel General
    Replies: 6
    Last Post: 11-10-2010, 01:37 AM
  5. A text box that formats a date
    By slmi1313 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2008, 12:39 AM

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