Date format of dates stored as text
Results 1 to 3 of 3

Thread: Date format of dates stored as text
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2009
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Date format of dates stored as text

    Does anyone know how to return the date format of dates stored as text?

    I have a column of dates delimited by commas which I split into individual strings. I then want to determine the date format for each string.

    Examples of desired results:

    If cell contains "301009" ---------------- then return dmy
    If cell contains "12/05/09" -------------- then return dd/mm/yy
    If cell contains "12/05/2009" ------------ then return dd/mm/yyyy
    If cell contains "05/21/09" -------------- then return mm/dd/yy
    If cell contains "Mon-10-2008" ---------- then return ddd-m-yyyy
    If cell contains "12 November 2009" ----- then return dd mmmm yyyy
    Thanks for you assistance

    Michael

  2. #2
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    6,012
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Date format of dates stored as text

    1) Strings stored as text don't have a "date format". They are strings. It's up to the human mind to interpret what the strings mean.

    Even in your example above you contradict "simple" logic with examples two and four...those two text strings are identical in layout, but you indicate they should return different "date formats".

    Discerning between those formats is easy for humans, but not to a formula or macro. If a text string says 5/3/09 and it's stored as text, a human has to determine if that's May3rd or March5th.

    2) You can convert most of those text strings INTO actual dates with a TEXT TO COLUMNS trick, but as you've already noted, they aren't consistent:

    Sheet3

     AB
    1BeforeAfter
    2  
    330100910/30/2009
    412/05/095/12/2009
    512/05/20095/12/2009
    605/21/095/21/2009
    7Mon-10-2008Mon-10-2008
    812 November 200912-Nov-09


    Excel tables to the web >> Excel Jeanie HTML 4

    Steps:
    a) Highlight column of values
    b) Select Data > Text To Columns > Delimited > Next > Next
    c) Select Date: in the upper right corner "Column Data Format" and select DMY
    d) Click OK
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  3. #3
    Board Regular
    Join Date
    Nov 2009
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date format of dates stored as text

    Thanks for the reply.

    I see what you mean about interpreting strings different ways and the limits of what Excel can do in that respect.

    For those strings that can be converted into dates, how would I then flag via VBA or formula what date format they are in?

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
  •