Parse and convert with odd format date and time

[ekidder]

Hello all,

I am trying to convert date and time to CST from ZULU with the date. The export gives me 2017-09-30T05:00:00.000Z. Which is 12:00 AM on 9/30/17. I need a formula that will do that. I did try left, right, mid, value and a few more, but I keep getting ###### in the cell.

The only way I was able to get it to work was to use text to columns two times and piece it together. I also tried to use a macro to do it, but it does not seem to record the actions. I work on a Mac, and have never done so, until now.

Thank you guys!

 

[sheetspread]

I keep getting ###### in the cell.

If you paste as a value what's in the bar?


Does this work?

Excel 2010
	A	B
1	2017-09-30T05:00:00.000Z	9/29/17 18:00
Sheet19

Cell Formulas
Range	Formula
B1	=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIMEVALUE(MID(A1,15,9))-0.25

 

[Tetra201]

Try

=LEFT(A1,10)+MID(A1,12,8)-"5:00"

 

[sheetspread]

Try

=LEFT(A1,10)+MID(A1,12,8)-"5:00"

=LEFT(A1,10)+MID(A1,12,8)-"11:00" if CST = UTC-6:00 (I think)

good formula either way

 

[ekidder]

This works. Did not think of doing this! You guys are the best!

 

[ekidder]

It would go ### if I use the value formula