|
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，以正确地显示时间。
|
|
|
来自： hercules028 > 《excel》
