将Excel中的长日期字符串转换为可排序的日期/时间格式 [英] Convert long date strings in Excel into a sortable date/time format
问题描述
我有一个Excel电子表格,其中充满了文本日期,格式如下:
I have an Excel spreadsheet full of text dates formatted like this:
2015年4月23日,星期四,上午9:19
由于它们是文本,因此Excel无法按时间顺序对其进行排序.如何将它们转换为Excel可以使用的格式?
Because they're text, Excel can't sort them chronologically. How do I convert them into a format Excel can work with?
还有,我将来的参考是,这个日期/日期/时间格式有一个名字吗?
Also, for my future reference, is there a name for this day/date/time format?
推荐答案
如果格式一致,您也可以尝试以下方法:-
You could also try something like this, if the formats are consistent:-
=SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("day, ",A1)-4),",","")
至少根据尽管由于它是一个日期时间值,所以应该正确地作为一个列进行排序,但它确实会发出警告消息,因为Excel不知道是将其视为数字还是文本,因此,更好的答案是
Although this should sort correctly as a single column since it is a date-time value, it does give a warning message because Excel doesn't know whether to treat it as a number or text, so a better answer would be
=SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("day, ",A1)-4),",","")+0
然后,您可以使用任何所需的格式对其进行格式化,因此在英国,您可以选择以下自定义格式:-
You can then format it in whatever way you want, so in the UK you would choose the following custom format:-
dd/mm/yyyy hh:mm
结果
23/04/2015 09:19
这篇关于将Excel中的长日期字符串转换为可排序的日期/时间格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!