如何将Excel日期/时间转换为历元

I Googled and found a formula that is supposed to convert from Excel date/time to epoch time. However, it's off (or I'm off) and I can't figure out what I'm doing wrong. I'm using http://www.epochconverter.com/ as my source of truth.

我在谷歌上搜索了一下，发现了一个公式，它应该是从Excel date/time转换到epoch time。但是，它是关闭的(或者我关闭了)，我不知道我做错了什么。我使用http://www.epochconverter.com/作为我的真理来源。

So the link I found says the formula is:

所以我发现的联系是公式是:

=(A1-25569)*86400

I think 25569 is the Excel value for DATEVALUE("1-1-1970") but for some reason my version of Excel says that's 24107 so I made some modifications and typed:

我认为25569是DATEVALUE的Excel值(“1-1-1970”)，但由于某些原因，我的Excel版本说这是24107，所以我做了一些修改，然后输入:

=((A2-DATEVALUE("1/1/1970"))*86400 - 8*3600 (for PST)

Now the number is a little more correct. However, it seems like I need to ADD 8*3600 instead of SUBTRACT.

现在这个数字更准确了。然而，似乎我需要加8*3600而不是减法。

Can someone explain to me why?

有人能解释一下为什么吗?

I thought PST is -8 from GMT.

我以为PST是-8从格林尼治标准时间。

1 个解决方案

#1

For the sake of an answer:

为了回答:

Your Excel is using the 1904 date system.

你的Excel使用的是1904年的日期系统。

IF your Excel times start off as ‘local’ don’t adjust for time zone. IF they start as GMT (a concept Excel does not have) then, because when a GMT clock ticks on to 16:00 a ‘local’ clock should tick on to 08:00, from Excel to ‘local’ means deducting 8 hours. Note that midnight 31/12/1969 is not the same instant GMT as PST.

如果你的Excel以“本地”开头，不要根据时区进行调整。如果它们以GMT开头(一个概念Excel没有)，那么，因为当GMT时钟滴答到16:00时，“本地”时钟应该滴答到08:00，从Excel到local意味着减去8小时。请注意，1969年12月31日午夜并不是与PST相同的即时格林尼治标准时间。

#1