Converting Text to Military Date Time Group Format

I am trying to find a way to have excel recognize text data input as date/time.

Please Login or Register  to view this content.

Where 02 Is the Date, 2020 is the time (military) Z is Zulu/GMT, MAR is Month and 14 is Year.
I believe excel recognizes

Please Login or Register  to view this content.

but I have a spreadsheet (on a confidential system) with thousands of entries that I need to convert.
Also, the people I have working for me are not remotely
excel savvy and would reject the idea of entering data that way.
I am also trying to keep the display the same format: ddhhmm"Z" MMM yy

etaf, thanks for the quick response

The timezone on the machine is Central European Time (Rome)

I tested:

Please Login or Register  to view this content.

The result I seem to get is an appropriate text representation of the data, but not an actual date/time values

this is demonstrated in the attached spreadsheet, where I put the dataset into a table and attempted to sort by the converted data.

So to clarify, my goals were to keep the same format, be able to enter the data in said format, and have excel recognize the entered DTG as Date/Time values.

I appreciate the assistance,
Brian

DTG TEST.xlsx

etaf, once again thanks for the quick reply

It does not seem to be converting correctly. Also in my table, once it exceeds row 9, it exports the #VALUE! error. I'm trying to look at the code to see if I can debug as well.

Ah, sorry forgot to answer those. The format is always entered the same. In military signals, they always use to the same format to serialize which is the DTG. Thus, they always follow DDHHMM"Z" MMM YY format and we enter them as such for tracking. So in that case, this works perfectly for my intentions. Thanks again for the help. Also, that's a clever idea with the font. I'm also thinking about looking into the VBA option to convert the data text as it is entered, but also not my area of expertise.