Posts Tagged"Unix"

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中自动转换的方法。

Read the rest of this entry

Back to Page Top