时间转换字符串_问与答84： 如何将文本转换为时间？

学习Excel技术，关注微信公众号：

excelperfect

Q：在我从其他地方导入了一些数据到Excel中，我想将它们转换成时间。如下图1所示，这些数据字符串的长度可能是1、3、4、5或6。如何编写这个公式完成这样的转换？

图1

(注：本案例整理自chandoo.org论坛。)

A：我们首先分析这些数据，看看有没有什么规律。

我们能看到：

235900要转换成23时59分0秒

10900要转换成1时9分0秒

1000要转换成0时10分0秒

100要转换成0时1分0秒

0要转换成0时0分0秒

可以考虑如何使用Left、Right和Mid函数从文本中分别提取小时、分钟和秒。由于字符串长度的变化，需要考虑公式适应每个字符串长度，它们最终将变得很复杂。尝试使用前导0填充字符串然后结合TIME函数提取，公式会很长。最后尝试使用TEXT函数，得到公式：

=(LEFT(TEXT(A2,"000000"),2)/24)+(MID(TEXT(A2,"000000"),3,2)/1440)+(RIGHT(TEXT(A2,"000000"),2)/(24*3600))

向下拖至合适的单元格。

在Excel中，时间只是0(=午夜)到0.999999(=11:59:59pm)之间的一个数字，因此可以分别提取小时、分钟和秒，然后将它们简单地相加即可得出实际时间。

可以使用TEXT函数以一致的格式显示字符串，从而允许使用Left、Mid和Right函数从适当的位置获取时、分和秒。

对于小时部分：

LEFT(TEXT(A2,"000000"),2)/24

TEXT函数将数据转换成235900、010900、001000、…等，接着LEFT函数从中提取前2个字符，这样：

LEFT(235900,2)=23

LEFT(010900,2)=01

LEFT(001000,2)=00

通过除以24转换成代表小时的时间序数。

同样，对于分钟部分：

MID(TEXT(A2,"000000"),3,2)/1440

使用TEXT函数转换数据后，传递给MID函数取中间的2个字符：

MID(235900,3,2)=59

MID(010900,3,2)=09

MID(001000,3,2)=10

通过除以1440转换成代表分的时间序数。(注：1440是一天中的分钟数，即24*60)

同样，对于秒部分：

RIGHT(TEXT(A2,"000000"),2)/(24*3600)

使用TEXT函数转换数据后，传递给RIGHT函数取末尾的2个字符：

RIGHT(235900,2)=00

RIGHT(010900,2)=00

RIGHT(001000,2)=00

通过除以86400转换成代表秒的时间序数。(注：86400是一天中的秒数，即24*60*60)

将上述3个式子相加得到总的时间。注意，将单元格格式设置为时间格式：h:mm:ss，以正确地显示时间。