Results 1 to 9 of 9

20101001, 05:49 #1
 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

20101001, 06:17 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 Posts
What is the relevance of the Z? Is it always present and always in the same location?
Regards,
Rory
Microsoft MVP  Excel

20101001, 06:47 #3
 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 datevalue to look like the original text (while keeping it a date)with the custom format:
ddhhmm"Z "mmm yyyy

20101001, 07:03 #4
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,005
 Thanks
 2
 Thanked 406 Times in 335 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]

20101002, 07:08 #5
 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

20101002, 07:24 #6
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,005
 Thanks
 2
 Thanked 406 Times in 335 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]

20101003, 05:19 #7
 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

20101003, 06:29 #8
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,005
 Thanks
 2
 Thanked 406 Times in 335 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 texttonumber 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]

20101003, 07:58 #9
 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