LinkBack URL
About LinkBacks
I have a few columns with text in ISO8601 format such as 2016-04-30T06:48:52. Does a facility in Excel exist where the text can remain in the format but the column display as custom date/time and allow sorting?
Thanks!
I have a few columns with text in ISO8601 format such as 2016-04-30T06:48:52. Does a facility in Excel exist where the text can remain in the format but the column display as custom date/time and allow sorting?
Thanks!
Since the format is
YYYY-MM-DDTOHH:MM:SS
you can simply sort on the data as it is.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
If this data is text, you can't display that data any other way in place. You can create a new column that uses a formula like this to convert to date/time:
Formula:
=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))
and then format it however you want.
Special-K makes a great point. If all you care about is sorting, the original data is sortable since most significant values appear from the left.
Try this ...
=--SUBSTITUTE(A1,"T"," ")
Or ...
=--REPLACE(A1,11,1," ")
Ok, all those suggestions help a lot. Is it possible to devise a format string that hides the "T" much like the formula above, but without creating a new column or formula?
Thanks a lot everyone!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks