I have Excel documents containing Unix epoch timestamps from a Java application. I'd like to see what they translate to and represent them as human readable dates inside of Excel.
我有来自Java应用程序的包含Unix纪元时间戳的Excel文档。我想看看它们在Excel中被翻译成什么并表示为人类可读的日期。
For example, the following long: 1362161251894 should evaluate to something readable like: 01 Mar 2013 11:07:31,894
例如，下面的long: 1362161251894应该是可读的:01 Mar 2013 11:07:07:31894
I'm assuming I can create a formula for this, but I'm not sure how. Thanks!
我假设我可以为它创建一个公式，但是我不知道怎么做。谢谢!
4 个解决方案
#1
42
Yes, you can create a formula to do this for you. Java and Unix/Linux count the number of milliseconds since 1/1/1970 while Microsoft Excel does it starting on 1/1/1900 for Windows and 1/1/1904 for Mac OS X. You would just need to do the following to convert:
是的，你可以创建一个公式来为你做这个。Java和Unix/Linux从1/1/1970开始计算毫秒数，而Microsoft Excel从1/1/1900开始计算Windows, 1/1/1904开始计算Mac OS x。
For GMT time on Windows
为格林尼治时间在窗口
=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))
For GMT time on Mac OS X
在macosx上的GMT时间
=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))
For local time on Windows (replace t with your current offset from GMT)
对于Windows上的本地时间(用当前GMT偏移量替换t)
=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))
For local time on Mac OS X (replace t with your current offset from GMT)
在Mac OS X上的本地时间(用当前GMT偏移量替换t)
=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))
In your specific case it looks like you are in a Mountain Time (GMT offset of 7). So if I paste your value given of 1362161251894 in a new Excel spreadsheet in cell A1 and then paste the following formula, I get a result of 41333.46356, which if I then tell Excel to format as a Date (press ctrl+1 on the cell) is: 2/28/13 11:07 AM
在特定情况下看起来像你在山地时区(格林尼治时间抵消7)。如果我粘贴你的价值给1362161251894的新的Excel电子表格单元格A1中然后粘贴下面的公式,我得到的结果41333.46356,然后如果我告诉Excel格式为日期(按ctrl + 1在细胞)是:2/28/13 11:07
=(((A1/1000)-(7*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))
#3
2
Epoch time was provided in milliseconds (1523060201838). User is looking to Convert for NY Time Zone (GMT -04:00 or DST -14400).
纪元时间以毫秒为单位(1523060201838)。用户正在寻找转换为纽约时区(GMT -04:00或DST -14400)。
Cell A1 on Excel has the 13 digit value epoch time.
Excel上的A1单元格具有13位值历元时间。
((A1/1000)-14400)/86400 + 25569
converts to 4/6/18 8:16 PM (after you format cell as Date).
转换到4/6/18 8:16 PM(在您格式化单元格为日期之后)。
#4
0
Excel expresses days as whole values and times as fractional values. Therefore, if the epoch time was provided in milliseconds since 1-1-1970 from UNIX, then we want to divide by the number of milliseconds in a year and add Excel's representation of 1-1-1970 to provide the human readable UTC time. If your value is in cell A1, then Excel would need:
Excel将天数表示为整数值，时间表示为小数。因此，如果从UNIX开始，以毫秒为单位提供纪元时间，那么我们需要除以一年的毫秒数，并添加Excel的1-1-1970表示，以提供人类可读的UTC时间。如果您的值在A1单元格中，则Excel需要:
=A1/86400/1000+DATEVALUE("1-1-1970")
Note you can drop the /1000 if your epoch time was in seconds rather than milliseconds. To convert to local time, where 't' is your local UTC offset (remember to use a negative value if you have a negative UTC offset), you can add/subtract the UTC offset:
注意，如果您的历元时间是秒，而不是毫秒，您可以删除/1000。要转换为本地时间，其中't'是您的本地UTC偏移量(如果您的UTC偏移量为负，请记住使用负值)，您可以添加/减去UTC偏移量:
=A1/86400/1000+DATEVALUE("1-1-1970")+t/24
Note that your UTC offset may vary based on whether daylight saving time is observed in your location, making this an incomplete solution for showing local times throughout the year in a single spreadsheet.
注意，您的UTC偏移量可能根据您的位置是否观察到夏令时而有所不同，这使得在一个电子表格中显示全年的本地时间成为一个不完整的解决方案。
#1
42
Yes, you can create a formula to do this for you. Java and Unix/Linux count the number of milliseconds since 1/1/1970 while Microsoft Excel does it starting on 1/1/1900 for Windows and 1/1/1904 for Mac OS X. You would just need to do the following to convert:
是的，你可以创建一个公式来为你做这个。Java和Unix/Linux从1/1/1970开始计算毫秒数，而Microsoft Excel从1/1/1900开始计算Windows, 1/1/1904开始计算Mac OS x。
For GMT time on Windows
为格林尼治时间在窗口
=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))
For GMT time on Mac OS X
在macosx上的GMT时间
=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))
For local time on Windows (replace t with your current offset from GMT)
对于Windows上的本地时间(用当前GMT偏移量替换t)
=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))
For local time on Mac OS X (replace t with your current offset from GMT)
在Mac OS X上的本地时间(用当前GMT偏移量替换t)
=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))
In your specific case it looks like you are in a Mountain Time (GMT offset of 7). So if I paste your value given of 1362161251894 in a new Excel spreadsheet in cell A1 and then paste the following formula, I get a result of 41333.46356, which if I then tell Excel to format as a Date (press ctrl+1 on the cell) is: 2/28/13 11:07 AM
在特定情况下看起来像你在山地时区(格林尼治时间抵消7)。如果我粘贴你的价值给1362161251894的新的Excel电子表格单元格A1中然后粘贴下面的公式,我得到的结果41333.46356,然后如果我告诉Excel格式为日期(按ctrl + 1在细胞)是:2/28/13 11:07
=(((A1/1000)-(7*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))
#2
#3
2
Epoch time was provided in milliseconds (1523060201838). User is looking to Convert for NY Time Zone (GMT -04:00 or DST -14400).
纪元时间以毫秒为单位(1523060201838)。用户正在寻找转换为纽约时区(GMT -04:00或DST -14400)。
Cell A1 on Excel has the 13 digit value epoch time.
Excel上的A1单元格具有13位值历元时间。
((A1/1000)-14400)/86400 + 25569
converts to 4/6/18 8:16 PM (after you format cell as Date).
转换到4/6/18 8:16 PM(在您格式化单元格为日期之后)。
#4
0
Excel expresses days as whole values and times as fractional values. Therefore, if the epoch time was provided in milliseconds since 1-1-1970 from UNIX, then we want to divide by the number of milliseconds in a year and add Excel's representation of 1-1-1970 to provide the human readable UTC time. If your value is in cell A1, then Excel would need:
Excel将天数表示为整数值，时间表示为小数。因此，如果从UNIX开始，以毫秒为单位提供纪元时间，那么我们需要除以一年的毫秒数，并添加Excel的1-1-1970表示，以提供人类可读的UTC时间。如果您的值在A1单元格中，则Excel需要:
=A1/86400/1000+DATEVALUE("1-1-1970")
Note you can drop the /1000 if your epoch time was in seconds rather than milliseconds. To convert to local time, where 't' is your local UTC offset (remember to use a negative value if you have a negative UTC offset), you can add/subtract the UTC offset:
注意，如果您的历元时间是秒，而不是毫秒，您可以删除/1000。要转换为本地时间，其中't'是您的本地UTC偏移量(如果您的UTC偏移量为负，请记住使用负值)，您可以添加/减去UTC偏移量:
=A1/86400/1000+DATEVALUE("1-1-1970")+t/24
Note that your UTC offset may vary based on whether daylight saving time is observed in your location, making this an incomplete solution for showing local times throughout the year in a single spreadsheet.
注意，您的UTC偏移量可能根据您的位置是否观察到夏令时而有所不同，这使得在一个电子表格中显示全年的本地时间成为一个不完整的解决方案。