blocks|key|1197049|text|如果我们假设Excel中的日期是格式为日期的A1单元格，而Unix时间戳应该是格式为数字的A2单元格，则A2中的公式应为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1197050|=+(A1+*+86400)+-+2209075200|1197051|其中：|1197052|86400是一天中的秒数，2209075200是介于1900-01-01和1970-01-01之间的秒数，这是Excel和Unix时间戳的基本日期。|1197053|对于Windows，上述情况是成立的。在Mac上，Excel中的基准日期为1904-01-01，秒数应更正为:+2082844800|1197054|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

If we assume the date in Excel is in A1 cell formatted as Date and the Unix timestamp should be in a A2 cell formatted as number the formula in A2 should be:

= (A1 * 86400) - 2209075200

where:

86400 is the number of seconds in the day
2209075200 is the number of seconds between 1900-01-01 and 1970-01-01 which are the base dates for Excel and Unix timestamps.

The above is true for Windows. On Mac the base date in Excel is 1904-01-01 and the seconds number should be corrected to: 2082844800

blocks|key|1197161|text|很明显，你差了一天，正好86400秒。使用数字2209161600而不是数字2209075200如果你在谷歌上搜索这两个数字，你会发现上面的支持。我尝试了你的公式，但总是出现与我的服务器不同的一天。从unix时间戳中看不出这一点，除非你考虑unix而不是人类时间;-)但如果你仔细检查，你会发现这可能是正确的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1197162|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You're apparently off by one day, exactly 86400 seconds.
Use the number 2209161600
Not the number 2209075200
If you Google the two numbers, you'll find support for the above.
I tried your formula but was always coming up 1 day different from my server. It's not obvious from the unix timestamp unless you think in unix instead of human time ;-) but if you double check then you'll see this might be correct.

blocks|key|1412373|text|Windows和Mac+Excel+(2011)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1412374|Unix+Timestamp+=+(Excel+Timestamp+-+25569)+*+86400
Excel+Timestamp+=++(Unix+Timestamp+/+86400)+%2B+25569|code-block|syntax|javascript|1412375|MAC+OS+X+(2007)：|1412376|Unix+Timestamp+=+(Excel+Timestamp+-+24107)+*+86400
Excel+Timestamp+=++(Unix+Timestamp+/+86400)+%2B+24107|1412377|供参考：|1412378|86400+=+Seconds+in+a+day
25569+=+Days+between+1970/01/01+and+1900/01/01+(min+date+in+Windows+Excel)
24107+=+Days+between+1970/01/01+and+1904/01/02+(min+date+in+Mac+Excel+2007)|1412379|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Windows and Mac Excel (2011):

<pre><code>Unix Timestamp = (Excel Timestamp - 25569) * 86400
Excel Timestamp = (Unix Timestamp / 86400) + 25569
</code></pre>

MAC OS X (2007):

<pre><code>Unix Timestamp = (Excel Timestamp - 24107) * 86400
Excel Timestamp = (Unix Timestamp / 86400) + 24107
</code></pre>

For Reference:

<pre><code>86400 = Seconds in a day
25569 = Days between 1970/01/01 and 1900/01/01 (min date in Windows Excel)
24107 = Days between 1970/01/01 and 1904/01/02 (min date in Mac Excel 2007)
</code></pre>

blocks|key|1412454|text|这些对我来说都不管用。当我将时间戳转换回来时，它是4年前的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1412455|这一切都很好用：=(A2-DATE(1970,1,1))*86400|offset|length|style|CODE|1412456|赞助人:+Filip+Czaja+http://fczaja.blogspot.ca|1412457|原文：http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html|1412458|entityMap|0|LINK|mutability|MUTABLE|url|http://fczaja.blogspot.ca/|1|http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html^0|0|8|Q|0|H|P|0|0|3|20|1|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@$D|X|E|Y|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@$D|10|E|11|1|12]]|A|$]]|$1|J|3|K|5|6|7|13|8|@]|9|@$D|14|E|15|1|16]]|A|$]]|$1|L|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]|T|$5|O|P|Q|A|$R|U]]]]

Non of these worked for me... when I converted the timestamp back it's 4 years off.

This worked perfectly: <code>=(A2-DATE(1970,1,1))*86400</code>

Credit goes to: Filip Czaja <a href="http://fczaja.blogspot.ca" rel="noreferrer">http://fczaja.blogspot.ca</a>

Original Post: <a href="http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html" rel="noreferrer">http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html</a>

blocks|key|1412520|text|这是我对这个问题的最终答案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1412521|显然，javascript的new+Date(year,+month,+day)构造函数也没有考虑到闰秒。|offset|length|style|CODE|1412522|//+Parses+an+Excel+Date+("serial")+into+a
//+corresponding+javascript+Date+in+UTC%2B0+timezone.
//
//+Doesn't+account+for+leap+seconds.
//+Therefore+is+not+100%25+correct.
//+But+will+do,+I+guess,+since+we're
//+not+doing+rocket+science+here.
//
//+https://www.pcworld.com/article/3063622/software/mastering-excel-date-time-serial-numbers-networkdays-datevalue-and-more.html
//+"If+you+need+to+calculate+dates+in+your+spreadsheets,
//++Excel+uses+its+own+unique+system,+which+it+calls+Serial+Numbers".
//
lib.parseExcelDate+=+function+(excelSerialDate)+{
++//+"Excel+serial+date"+is+just
++//+the+count+of+days+since+`01/01/1900`
++//+(seems+that+it+may+be+even+fractional).
++//
++//+The+count+of+days+elapsed
++//+since+`01/01/1900`+(Excel+epoch)
++//+till+`01/01/1970`+(Unix+epoch).
++//+Accounts+for+leap+years
++//+(19+of+them,+yielding+19+extra+days).
++const+daysBeforeUnixEpoch+=+70+*+365+%2B+19;

++//+An+hour,+approximately,+because+a+minute
++//+may+be+longer+than+60+seconds,+see+"leap+seconds".
++const+hour+=+60+*+60+*+1000;

++//+"In+the+1900+system,+the+serial+number+1+represents+January+1,+1900,+12:00:00+a.m.
++//++while+the+number+0+represents+the+fictitious+date+January+0,+1900".
++//+These+extra+12+hours+are+a+hack+to+make+things
++//+a+little+bit+less+weird+when+rendering+parsed+dates.
++//+E.g.+if+a+date+`Jan+1st,+2017`+gets+parsed+as
++//+`Jan+1st,+2017,+00:00+UTC`+then+when+displayed+in+the+US
++//+it+would+show+up+as+`Dec+31st,+2016,+19:00+UTC-05`+(Austin,+Texas).
++//+That+would+be+weird+for+a+website+user.
++//+Therefore+this+extra+12-hour+padding+is+added
++//+to+compensate+for+the+most+weird+cases+like+this
++//+(doesn't+solve+all+of+them,+but+most+of+them).
++//+And+if+you+ask+what+about+-12/%2B12+border+then
++//+the+answer+is+people+there+are+already+accustomed
++//+to+the+weird+time+behaviour+when+their+neighbours
++//+may+have+completely+different+date+than+they+do.
++//
++//+`Math.round()`+rounds+all+time+fractions
++//+smaller+than+a+millisecond+(e.g.+nanoseconds)
++//+but+it's+unlikely+that+an+Excel+serial+date
++//+is+gonna+contain+even+seconds.
++//
++return+new+Date(Math.round((excelSerialDate+-+daysBeforeUnixEpoch)+*+24+*+hour)+%2B+12+*+hour);
};|code-block|syntax|javascript|1412523|entityMap^0|0|E|Q|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Here's my ultimate answer to this.

Also apparently javascript's <code>new Date(year, month, day)</code> constructor doesn't account for leap seconds too.

<pre class="lang-js prettyprint-override"><code>// Parses an Excel Date ("serial") into a
// corresponding javascript Date in UTC+0 timezone.
//
// Doesn't account for leap seconds.
// Therefore is not 100% correct.
// But will do, I guess, since we're
// not doing rocket science here.
//
// https://www.pcworld.com/article/3063622/software/mastering-excel-date-time-serial-numbers-networkdays-datevalue-and-more.html
// "If you need to calculate dates in your spreadsheets,
// Excel uses its own unique system, which it calls Serial Numbers".
//
lib.parseExcelDate = function (excelSerialDate) {
 // "Excel serial date" is just
 // the count of days since `01/01/1900`
 // (seems that it may be even fractional).
 //
 // The count of days elapsed
 // since `01/01/1900` (Excel epoch)
 // till `01/01/1970` (Unix epoch).
 // Accounts for leap years
 // (19 of them, yielding 19 extra days).
 const daysBeforeUnixEpoch = 70 * 365 + 19;

 // An hour, approximately, because a minute
 // may be longer than 60 seconds, see "leap seconds".
 const hour = 60 * 60 * 1000;

 // "In the 1900 system, the serial number 1 represents January 1, 1900, 12:00:00 a.m.
 // while the number 0 represents the fictitious date January 0, 1900".
 // These extra 12 hours are a hack to make things
 // a little bit less weird when rendering parsed dates.
 // E.g. if a date `Jan 1st, 2017` gets parsed as
 // `Jan 1st, 2017, 00:00 UTC` then when displayed in the US
 // it would show up as `Dec 31st, 2016, 19:00 UTC-05` (Austin, Texas).
 // That would be weird for a website user.
 // Therefore this extra 12-hour padding is added
 // to compensate for the most weird cases like this
 // (doesn't solve all of them, but most of them).
 // And if you ask what about -12/+12 border then
 // the answer is people there are already accustomed
 // to the weird time behaviour when their neighbours
 // may have completely different date than they do.
 //
 // `Math.round()` rounds all time fractions
 // smaller than a millisecond (e.g. nanoseconds)
 // but it's unlikely that an Excel serial date
 // is gonna contain even seconds.
 //
 return new Date(Math.round((excelSerialDate - daysBeforeUnixEpoch) * 24 * hour) + 12 * hour);
};
</code></pre>

Does anyone know how to convert an Excel date to a correct Unix timestamp?

问Excel日期到Unix时间戳
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel日期到Unix时间戳EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel日期到Unix时间戳
EN