0

I am given a date and time in this format

2014-08-29T00:00:00.000-07:00

And I can't figure out how to get MS Excel 2013 to acknowledge it as a date, even when I try to force Excel to acknowledge it as a custom data/time field. I believe the reason is because I am not using the symbols correctly. However I can't, for the life of me, find the right symbols!

enter image description here

E.S.
  • 2,733
  • 6
  • 36
  • 71
  • That will **never** be accepted as a true datetime in Excel. You can a) run a macro to convert it to a real datetime and apply a custom [Number Format Code](https://support.office.com/en-us/article/Number-format-codes-5026BBD6-04BC-48CD-BF33-80F18B4EAE68) to make it *look* like the original or you can use a 'helper' column that parses the datetime out of the original to be used in calculations. Applying a custom number to a string-that-looks-like-a-date will **not** magically convert it to a date. –  Mar 05 '16 at 08:35
  • Maybe this helps: http://stackoverflow.com/questions/4896116/parsing-an-iso8601-date-time-including-timezone-in-excel Just for the record, 2014-08-29T00:00:00.000-07:00 is an ISO8601 representation of time. – Sandman Mar 05 '16 at 11:10

1 Answers1

1

You could just extract the date and time values from the text and convert them to a datetime value.

Assuming that your textual date value is in the A1 cell:

=DATE(MID(A1;1;4);MID(A1;6;2);MID(A1;9;2)) + TIME(MID(A1;12;2);MID(A1;15;2);MID(A1;18;2))

convert text to excel date

To view both the date and the time, you will have to apply the following format in the Custom category of the Format Cells wizard: d/mm/yyyy h:mm

Limitations: I dropped the miliseconds and time-zone. I assume you won't be using these anyway.

bvdb
  • 22,839
  • 10
  • 110
  • 123