您在Excel中使用的某些数据可能从办公室中大型系统的输出开始。有时Excel可能会完全误解大型系统使用的日期格式。例如,输出可以提供格式为yyyydddtttt的日期,其中yyyy是年份,ddd是年份的序号日期(1到366),而tttt是基于24小时制的时间。乍一看,您可能不知道如何将这样的日期转换为Excel可以使用的日期。

解决方案有很多方法。但是,也许最好的公式如下:

=DATE(LEFT(A1,4),1,1)+MID(A1,5,3)-1+TIME(MID(A1,8,2),RIGHT(A1,2),0)

该公式首先计算出指定年份的1月1日的日期序列号,然后在该日期上加上正确的天数。

然后,公式将根据提供的内容计算正确的时间。

当调用这样的公式时,结果是日期序列号。

这意味着仍然需要对单元格进行格式化以显示日期格式。

只要您所掌握的信息有意义,那么这种方法就可以正常工作。例如,如果ddd确实在1到366的范围内,或者tttt是格式正确的24小时制时间,您将始终获得预期的结果。如果您预期原始数据可能会超出范围,那么最好的解决方案是创建一个自定义函数(使用宏),该函数将分解原始数据并检查为每个部分提供的值。

如果数据超出范围,则该函数可能会返回一个错误值,该错误值很容易在工作表中检测到。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(9241)适用于Microsoft Excel 2007、2010、2013、2016、2019和Office 365中的Excel。您可以在此处为Excel的较早菜单界面找到此技巧的版本: