20121029

如何在Excel中转换Unix epoch时间格式为可读时间格式

Author:  WinkCategories:  SoftwaresNo Comments »

最近在处理从Java项目导出的XML及XLSX文件时,发现导出的文件中,时间均为一长串数字,这其实是Unix epoch时间,即Unix Time。在维基百科上有其解释:

Unix time, or POSIX time, is a system for describing instances in time, defined as the number of seconds that have elapsed since midnight Coordinated Universal Time (UTC), 1 January 1970, not counting leap seconds. It is used widely in Unix-like and many other operating systems and file formats. It is neither a linear representation of time nor a true representation of UTC. Unix time may be checked on some Unix systems by typing date +%s on the command line.

该时间戳表达方法有13位和10位两种版本,例如PHP使用的是10位版本,而像JAVA等一些语言使用的是精度较高的13位版本。

随之而来的问题是,该如何在Excel中用可读格式来展现这些时间呢?有些网站其实利用了JavaScript等方式实现了在线转换的功能,例如FreeFormatter.com,可以输入13位代码来即时转换。不过这样一个个转换很繁琐,所以需要一种可以在Excel中自动转换的方法。

首先要确定换算公式,13位的换算公式为:

((A1+(X*3600000))/86400000)+25569

10位的换算公式为:

((A1+(X*3600))/86400)+25569

其中,A1为Excel中Unix epoch时间的单元格;X为时区(例如北京为8、北美东部标准时间区为-5等);3600为每小时秒数(与精度有关);86400为每天秒数(与精度有关);25569较为复杂一些,Excel计算epoch时间的起始日期为1900-01-01,但其他语言中该起始日期为1970-01-01,所以25569是这70年中天数的补差。

了解了公式之后,就可以在Excel中设置自动换算了。这里假设所有Unix epoch时间为13位,并且都在A列,换算后的时间在B列,时区为东8区。在Excel中先将B列的单元格设置为日期格式:

设置日期格式

然后在B1编辑设置计算公式为:

((A1+(8*3600000))/86400000)+25569

编辑公式

设置好之后,该单元格(B1)已经可以自动将A1的时间转换为形如“2012/8/31 9:08”的可读格式了。然后拖动该单元格右下角,将其应用于B列所有单元格,这应该算是Excel的基本操作。

至此,时间转换已完成,还是很便捷的。

You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Back to Page Top