+ Reply to Thread
Results 1 to 11 of 11

String to Date Conversion

  1. #1
    Registered User
    Join Date
    11-17-2010
    Location
    hollywood, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Smile String to Date Conversion

    How can I interpret this algorithm:

    Wed Nov 17 01:44:07 +0000 2010

    so that Excel will recognize it as a date & time.

    Format Cells =>Number => custom ...then what do I enter????

    Please help ASAP!!!!!!!!!!!!!!!!!
    Last edited by DonkeyOte; 11-18-2010 at 03:41 AM. Reason: modified title

  2. #2
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Recognize: Wed Nov 17 01:44:07 +0000 2010

    What about this:
    =DATEVALUE(MID(A1,5,3)&" "&MID(A1,9,2)&", "&RIGHT(A1,4))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

    Ensure that all entries follow the same format. All variables are on the same spot, that the month and day is always 3 and 2 char respectively; even if is September 6, it will be Sep 06, etc.

    Welcome by the way. Take some time to read the rules. Your post's tittle should describe your problem and not the desired solution
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Recognize: Wed Nov 17 01:44:07 +0000 2010

    You could shorten that given coercion etc and known string lengths:

    Please Login or Register  to view this content.
    (the only unknown is dd or d but the above should account for either/or)

  4. #4
    Registered User
    Join Date
    11-17-2010
    Location
    hollywood, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: String to Date Conversion

    I don't think that my problem has been adequately answered ....
    In one cell, I have as follows:
    Wed Nov 17 01:10:55 +0000 2010

    I am looking for info to input into the "format cells" menu so that it can interpret this data as a date/time for times like "sorting" data

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

    Re: String to Date Conversion

    no chance! it workd the other way around only
    the date value which is a number can be formatted to display in different ways.
    "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

  6. #6
    Registered User
    Join Date
    11-17-2010
    Location
    hollywood, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: String to Date Conversion

    There is a custom tab...
    Couldn't a date/time algorithm be formulated IN the custom tab?

  7. #7
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: String to Date Conversion

    The custom number format section is to format dates. Excel does not recognize your string as date but as a text string, so in order to convert it to a date you'll need a formula. Once excel has a date; then you can use the custom format and show it whichever way you see fit. Hope this made it clear why you cannot format your text string.

  8. #8
    Registered User
    Join Date
    11-17-2010
    Location
    hollywood, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: String to Date Conversion

    that makes sense now...could someone walk me through the steps necessary to make this happen?

  9. #9
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: String to Date Conversion

    I would suggest you read this page first

    Enter your data in A1 and enter the formula supplied by DO in the cell were you want the answer

  10. #10
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: String to Date Conversion

    Quote Originally Posted by dasreinfeld View Post
    that makes sense now...could someone walk me through the steps necessary to make this happen?
    Well, I'd insert a column after your date-text-string column and convert it to an excel proper date using DKO's formula above, copy this new column and paste values to your original column, and then delete this helper column.

    Then you can use Custom format if you don't see an option under Date that satisfies your demands.

  11. #11
    Registered User
    Join Date
    11-17-2010
    Location
    hollywood, CA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: String to Date Conversion

    mission accomplished. Thanks guys!

+ 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