Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is the cell value that I have that I need to convert; however, there doesn't seem to be a date type that can match:

    011341Z Oct 2010

    That is actually October 1, 2010 1:41 PM.

    I want it to be a valid date type such as 3/14/01 13:30, but the month needs to be converted from Oct to 10.

    What is the most optimal way, using one formula, of converting it to a valid date type in MS Excel?

    I can use some FIND, LEN, LEFT, RIGHT and MID formula arrays and combine the results, but it seems I would need an intermediary step of a VLOOKUP table to convert Oct to a number.

    Any help is greatly appreciated.

    Thanks
    Amy

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What is the relevance of the Z? Is it always present and always in the same location?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This works for your example:
    =DATEVALUE(SUBSTITUTE(MID(A1,9,8)," "," "&LEFT(A1,2)&", "))+TIMEVALUE(REPLACE(MID(A1,3,4),3,0,":"))

    Format it as:
    mmmm d, yyyy h:mm AM/PM

    to display as in your example

    I ignore the "Z" and presume that it (or some other character) is always there

    Steve

    PS. You can format that date-value to look like the original text (while keeping it a date)with the custom format:
    ddhhmm"Z "mmm yyyy

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Amy,

    Try this formula:
    =--SUBSTITUTE(LEFT(A1,2)&" "&RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&TEXT(--MID(LEFT(A1,FIND(" ",A1)-1-(CODE(RIGHT(LEFT(A1,FIND(" ",A1)-1)))>64)),3,4),"00Z00"),"Z",":")
    with a cell format of:
    mm/dd/yyyy h:mm AM/PM

    The above formula caters for any single letter that may (or may not) exist immediately after the time string.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Paul,
    That worked. I attached a file.

    =--SUBSTITUTE(LEFT(A1,2)&" "&RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&TEXT(--MID(LEFT(A1,FIND(" ",A1)-1-(CODE(RIGHT(LEFT(A1,FIND(" ",A1)-1)))>64)),3,4),"00Z00"),"Z",":")

    I don't know what the hyphens are all about.

    Rory,
    The "Z" refers to Zulu or Greenwich Mean Time

    Steve,
    No dice as I attempted your solution. It is TRUE, though

    You guys are great.

    I can see the next step when I get around to it, will be working this into an MS Access solution. Would that even be possible?

    Amy

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Amy,

    The hyphens, as you call them, are minus signs and serve to convert the text strings in the formula to +ve numeric values.

    Actually, the second pair could be deleted, thus:
    =--SUBSTITUTE(LEFT(A1,2)&" "&RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&TEXT(MID(LEFT(A1,FIND(" ",A1)-1-(CODE(RIGHT(LEFT(A1,FIND(" ",A1)-1)))>64)),3,4),"00Z00"),"Z",":")
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Paul,
    Thanks. As I break this down, the SUBSTITUTE seems to be the primary formula that converts the text string to a number value that is recognized by MS Excel and renders it capable to be converted into a date/time value.

    Then it looks like the CODE formula converts the Oct (or any three letter month) text string into the number month value. The CODE formula array returns the value 90 by itself irrespective of the month though.

    Not sure what the TEXT formula is doing.

    Also, what does the >64 function perform and why is are two zeroes preceding and proceeding the Z in the formula.

    What is your +ve reference.

    Thanks
    Amy

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Amy,

    To understand what's going on, you need to work from the outside inwards. The SUBSTITUTE is, in fact, the penultimate operation - the '--' converts the text string returned by SUBSTITUTE into a +ve numeric value (ie positive number).

    The SUBSTITUTE replaces the 'Z' I inserted as part of the TEXT formula that formats the time part of the expression as '00Z00'. Nowhere is there a text-to-number conversion for the month - that's actually taken care of by the '--'. If you delete the '--', you'll see a conventional date & time text string.

    The (CODE(RIGHT(LEFT(A1,FIND(" ",A1)-1)))>64) expression looks for the last character before the first space in your data and, if it has an ASCII value greater than 64 (which means it's not a number), returns TRUE (1) or FALSE (0). The returned result is deducted from the preceding value, which counts how many characters the 'LEFT' expression should include. Since you had a 'Z' in your data (ASCII 90), 1 is deducted. If your values always have the 'Z', you could simplify the formula by changing '-1-(CODE(RIGHT(LEFT(A1,FIND(" ",A1)-1)))>64)' to '-2'.

    The balance of the formula is concerned with getting the original data into the right order for a date & time string.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Paul,
    Thank you for the thorough explanation.

    Amy

Posting Permissions

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