How to convert a date and time format to excel?

Welcome to SO in Spanish. You only have an example of how the dates come, so maybe it will not work for you, but let's try.

I have divided everything into columns for ease, but you could conquer everything in a single formula if you wanted to and format the date and time to see the final result.

Well, your main problem is the T, the Z and the point in the seconds, since, unless your Excel is Anglo-Saxon, surely your decimal point is the comma and not the point.

I have divided the data into 3 columns. In the first one I get the position of the letter T with the following formula:

=ENCONTRAR("T";C2;1)

In the second column, I extract all the text from the first character to a position before where the letter T is, and I convert it into a date. I use the following formula:

=TEXTO(IZQUIERDA(C2;D2-1);"dd/mm/aaaa")

With this we would already have in a column what is the date.

Now, in the third column we will obtain the time. We extract all the text from a position after the letter T to the end of the text string minus 1 (to exclude the Z). In that extraction, we replace the point with a comma, so that Excel recognizes it as a number with decimal, and we convert it to hour format. The formulas used are:

=TEXTO(REEMPLAZAR(EXTRAE(C2;D2+1;LARGO(C2)-D2-1);9;1;",");"hh:mm:ss")

And so we get the time. The rest depends on how you want the data. If you want them in the same cell, for example, add both columns and format them dd/mm/aaaa hh:mm:ss . I did it this way in the fourth column to obtain the final result and that Excel recognizes it as a date:

I put a screenshot of the results:

More information about the functions I have used to process the data:

  

Function   REPLACE

     

Function   FIND

     

Function   TEXT

     

Function   LEFT

     

Function   EXTRACT

     

Function   LONG