Non-Date Thread Conversion Date Format

Topic

New Reply

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

Reply | Quote

Replies

What is the relevance of the Z? Is it always present and always in the same location?

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

Paul,
Thank you for the thorough explanation.

Amy

Reply | Quote