I got CSV report that has dateTime stamp on it (2020-10-26T18:32:21Z) MS Excel does not recognize it as the date, is there an easy way to get MS recognize as DATE data type?
Thank you
Asked
Active
Viewed 52 times
-3
-
you will need to parse the data to a recognizable string. This particular format to date parse has been answered many times on this site. – Scott Craner Oct 28 '20 at 15:01
-
1`=--SUBSTITUTE(SUBSTITUTE(A1,"T"," "),"Z","")` – BigBen Oct 28 '20 at 15:01
-
Does this answer your question? [Excel Date to String conversion](https://stackoverflow.com/questions/2636157/excel-date-to-string-conversion) – Adam Hoelscher Oct 28 '20 at 15:03
-
@Adam that is the reverse of what the OP wants. – Scott Craner Oct 28 '20 at 15:06
1 Answers
0
With data in A1, try:
=DATEVALUE(LEFT(A1,10))
a slight change would be needed if you also needed the time =DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8))
Gary's Student
- 95,722
- 10
- 59
- 99