将Google Sheets文本格式日期时间转为可计算时长的日期格式

我完全懂你现在的头疼——几百条数据的日期时间变成了07/May/18 07:14这种文本格式，自动转格式、DATEVALUE都不管用，手动改根本不现实。别慌，用两个函数组合就能批量搞定！

第一步：把文本格式转成Google Sheets可识别的日期时间值 #

假设你的「Created」时间在A列，「Updated」时间在B列，先把这些文本转成有效的日期时间值：

方法1：拆分提取各部分（兼容性拉满） #

在C2单元格（对应A2的转换值）输入公式：

=DATE(2000+RIGHT(A2,2),MATCH(MID(A2,4,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),LEFT(A2,2)) + TIME(MID(A2,9,2),RIGHT(A2,2),0)

下拉填充所有行，再把公式里的A2换成B2，放到D列（对应Updated的转换值）。

公式拆解：

• RIGHT(A2,2)：提取年份后两位（比如18），加2000得到完整年份2018
• MID(A2,4,3)：提取月份英文缩写（比如May），用MATCH匹配成数字5
• LEFT(A2,2)：提取日期（比如07）
• DATE(...)组合成日期，TIME(...)提取时分，两者相加得到完整的日期时间值

方法2：替换分隔符简化公式 #

如果你的日期格式是DD/MMM/YY HH:mm，可以用更简洁的写法：

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2,"/"," ",1),"/"," ",1)) + TIMEVALUE(RIGHT(A2,5))

原理是把07/May/18转成07 May 18，让DATEVALUE能识别，再加上TIMEVALUE提取的时间部分。

第二步：计算时长差值并转为HH:mm格式 #

等C、D列都变成有效的日期时间值后，在E2单元格输入：

=TEXT(D2-C2,"HH:mm")

下拉填充，就能得到你想要的00:20这种时长格式啦！

小提示：如果你的年份是四位（比如07/May/2018），只需要把方法1里的2000+RIGHT(A2,2)改成RIGHT(A2,4)就行。

内容的提问来源于stack exchange，提问作者PerilOS