Non-Typical Date format for excel

Question

I need to convert a date format into a date format that excel understands. This format I'm using is an export from a military system that spits the date out in a "military style" date format.

I need this: 011000Z OCT20 to equal 01-OCT-20.

The original format is this: DD HHMM(time Zone) MMM YY; so 01 1000Z(GMT Time) OCT 20

How can I make Excel recognize this as a date?

Currently, I'm using the Text to column feature to separate out the date, dump the time, then concatenate the multiple column into a date format. I then have to copy paste the new text into a date formatted column.

I have over 300 rows of date to go through, and its usually 100 rows weekly.

MACROS are not allowed on my systems. So this will have to be formulas.

Just looking for a more automated / simplified method that can keep up with the system exports.

Thanks everyone!!

Frank

Answer 1

=--REPLACE(A1,3,5,"")

will remove the time portion and convert to a date.

You'll need to format the result as a date

Answer 2

If one has TEXTJOIN:

=--TEXTJOIN("-",TRUE,FILTERXML("<a><b>"&SUBSTITUTE(A1," ","</b><b>")&"</b></a>","//b["&{1,4,5}&"]"))

Depending on one's version this may require the formula to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

then format the cell: dd-mmm-yy