blocks|key|329875|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|329876|function+ExcelDateToJSDate(serial)+{
+++var+utc_days++=+Math.floor(serial+-+25569);
+++var+utc_value+=+utc_days+*+86400;++++++++++++++++++++++++++++++++++++++++
+++var+date_info+=+new+Date(utc_value+*+1000);

+++var+fractional_day+=+serial+-+Math.floor(serial)+%2B+0.0000001;

+++var+total_seconds+=+Math.floor(86400+*+fractional_day);

+++var+seconds+=+total_seconds+%25+60;

+++total_seconds+-=+seconds;

+++var+hours+=+Math.floor(total_seconds+/+(60+*+60));
+++var+minutes+=+Math.floor(total_seconds+/+60)+%25+60;

+++return+new+Date(date_info.getFullYear(),+date_info.getMonth(),+date_info.getDate(),+hours,+minutes,+seconds);
}|code-block|syntax|javascript|329877|为您定制:)|329878|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try this:

<pre><code>function ExcelDateToJSDate(serial) {
 var utc_days = Math.floor(serial - 25569);
 var utc_value = utc_days * 86400; 
 var date_info = new Date(utc_value * 1000);

 var fractional_day = serial - Math.floor(serial) + 0.0000001;

 var total_seconds = Math.floor(86400 * fractional_day);

 var seconds = total_seconds % 60;

 total_seconds -= seconds;

 var hours = Math.floor(total_seconds / (60 * 60));
 var minutes = Math.floor(total_seconds / 60) % 60;

 return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
}
</code></pre>

Custom made for you :)

blocks|key|329883|text|我为你写了一句话：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|329884|function+ExcelDateToJSDate(date)+{
++return+new+Date(Math.round((date+-+25569)*86400*1000));
}|code-block|syntax|javascript|329885|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I made a one-liner for you:

<pre><code>function ExcelDateToJSDate(date) {
 return new Date(Math.round((date - 25569)*86400*1000));
}
</code></pre>

blocks|key|112407|text|//+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);
};|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|112408|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<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>

blocks|key|112446|text|虽然我在这个讨论开始几年后偶然发现了这个问题，但我可能对最初的问题有一个更简单的解决方案--这里是我最终将Excel“自1899-12-30以来的天数”转换为我需要的JS日期的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|112447|var+exdate+=+33970;+//+represents+Jan+1,+1993
var+e0date+=+new+Date(0);+//+epoch+"zero"+date
var+offset+=+e0date.getTimezoneOffset();+//+tz+offset+in+min

//+calculate+Excel+xxx+days+later,+with+local+tz+offset
var+jsdate+=+new+Date(0,+0,+exdate-1,+0,+-offset,+0);

jsdate.toJSON()+=>+'1993-01-01T00:00:00.000Z'|code-block|syntax|javascript|112448|本质上，它只是构建一个新的Date对象，通过添加Excel天数(从1开始)，然后通过负的本地时区偏移量调整分钟数来计算该对象。|112449|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Although I stumbled onto this discussion years after it began, I may have a simpler solution to the original question -- fwiw, here is the way I ended up doing the conversion from Excel "days since 1899-12-30" to the JS Date I needed:

<pre><code>var exdate = 33970; // represents Jan 1, 1993
var e0date = new Date(0); // epoch "zero" date
var offset = e0date.getTimezoneOffset(); // tz offset in min

// calculate Excel xxx days later, with local tz offset
var jsdate = new Date(0, 0, exdate-1, 0, -offset, 0);

jsdate.toJSON() =&gt; '1993-01-01T00:00:00.000Z'
</code></pre>

Essentially, it just builds a new Date object that is calculated by adding the # of Excel days (1-based), and then adjusting the minutes by the negative local timezone offset.

blocks|key|329967|text|因此，我遇到了同样的问题，然后一些解决方案出现了问题，但在地区、时区等方面开始出现问题，但最终能够增加所需的精度|type|unstyled|depth|inlineStyleRanges|entityRanges|data|329968|toDate(serialDate,+time+=+false)+{
++++let+locale+=+navigator.language;
++++let+offset+=+new+Date(0).getTimezoneOffset();
++++let+date+=+new+Date(0,+0,+serialDate,+0,+-offset,+0);
++++if+(time)+{
++++++++return+serialDate.toLocaleTimeString(locale)
++++}
++++return+serialDate.toLocaleDateString(locale)
}|code-block|syntax|javascript|329969|函数的'+time‘参数在显示整个日期或仅显示日期时间之间进行选择|329970|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

So, there I was, having the same problem, then some solutions bumped up but started to have troubles with the Locale, Time Zones, etc, but in the end was able to add the precision needed 

<pre><code>toDate(serialDate, time = false) {
 let locale = navigator.language;
 let offset = new Date(0).getTimezoneOffset();
 let date = new Date(0, 0, serialDate, 0, -offset, 0);
 if (time) {
 return serialDate.toLocaleTimeString(locale)
 }
 return serialDate.toLocaleDateString(locale)
}
</code></pre>

The function's 'time' argument chooses between displaying the entire date or just the date's time

blocks|key|112531|text|这是一个旧的线程，但希望我可以节省您准备编写这个npm包的时间：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|112532|$+npm+install+js-excel-date-convert|offset|length|style|CODE|112533|包使用：|112534|const+toExcelDate+=+require('js-excel-date-convert').toExcelDate;
const+fromExcelDate+=+require('js-excel-date-convert').fromExcelDate;
const+jul+=+new+Date('jul+5+1998');

toExcelDate(jul);++//+35981+(1900+date+system)

fromExcelDate(35981);+//+"Sun,+05+Jul+1998+00:00:00+GMT"|code-block|syntax|javascript|112535|您可以使用https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system上的示例验证这些结果|112536|代码：|112537|function+fromExcelDate+(excelDate,+date1904)+{
++const+daysIn4Years+=+1461;
++const+daysIn70years+=+Math.round(25567.5+%2B+1);+//+%2B1+because+of+the+leap-year+bug
++const+daysFrom1900+=+excelDate+%2B+(date1904+?+daysIn4Years+%2B+1+:+0);
++const+daysFrom1970+=+daysFrom1900+-+daysIn70years;
++const+secondsFrom1970+=+daysFrom1970+*+(3600+*+24);
++const+utc+=+new+Date(secondsFrom1970+*+1000);
++return+!isNaN(utc)+?+utc+:+null;
}

function+toExcelDate+(date,+date1904)+{
++if+(isNaN(date))+return+null;
++const+daysIn4Years+=+1461;
++const+daysIn70years+=+Math.round(25567.5+%2B+1);+//+%2B1+because+of+the+leap-year+bug
++const+daysFrom1970+=+date.getTime()+/+1000+/+3600+/+24;
++const+daysFrom1900+=+daysFrom1970+%2B+daysIn70years;
++const+daysFrom1904Jan2nd+=+daysFrom1900+-+daysIn4Years+-+1;
++return+Math.round(date1904+?+daysFrom1904Jan2nd+:+daysFrom1900);
}|112538|如果你想知道它是如何工作的，请查看：https://bettersolutions.com/excel/dates-times/1904-date-system.htm|112539|entityMap|0|LINK|mutability|MUTABLE|url|https://www.npmjs.com/package/js-excel-date-convert|1|https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system|2|https://bettersolutions.com/excel/dates-times/1904-date-system.htm^0|0|0|D|E|L|0|0|0|0|5|2C|1|0|0|0|I|1U|2|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@$D|1A|E|1B|F|G]]|9|@$D|1C|E|1D|1|1E]]|A|$]]|$1|H|3|I|5|6|7|1F|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1G|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1H|8|@]|9|@$D|1I|E|1J|1|1K]]|A|$]]|$1|Q|3|R|5|6|7|1L|8|@]|9|@]|A|$]]|$1|S|3|T|5|L|7|1M|8|@]|9|@]|A|$M|N]]|$1|U|3|V|5|6|7|1N|8|@]|9|@$D|1O|E|1P|1|1Q]]|A|$]]|$1|W|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]|14|$5|Z|10|11|A|$12|15]]|16|$5|Z|10|11|A|$12|17]]]]

It's an old thread but hopefully I can save you the time I used readying around to write this npm package: 

<code>$ npm install</code> <a href="https://www.npmjs.com/package/js-excel-date-convert" rel="nofollow noreferrer">js-excel-date-convert</a>

<h3>Package Usage:</h3>

<pre class="lang-js prettyprint-override"><code>const toExcelDate = require('js-excel-date-convert').toExcelDate;
const fromExcelDate = require('js-excel-date-convert').fromExcelDate;
const jul = new Date('jul 5 1998');

toExcelDate(jul); // 35981 (1900 date system)

fromExcelDate(35981); // "Sun, 05 Jul 1998 00:00:00 GMT"
</code></pre>

You can verify these results with the example at <a href="https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system</a>

<h3>The Code:</h3>

<pre class="lang-js prettyprint-override"><code>function fromExcelDate (excelDate, date1904) {
 const daysIn4Years = 1461;
 const daysIn70years = Math.round(25567.5 + 1); // +1 because of the leap-year bug
 const daysFrom1900 = excelDate + (date1904 ? daysIn4Years + 1 : 0);
 const daysFrom1970 = daysFrom1900 - daysIn70years;
 const secondsFrom1970 = daysFrom1970 * (3600 * 24);
 const utc = new Date(secondsFrom1970 * 1000);
 return !isNaN(utc) ? utc : null;
}

function toExcelDate (date, date1904) {
 if (isNaN(date)) return null;
 const daysIn4Years = 1461;
 const daysIn70years = Math.round(25567.5 + 1); // +1 because of the leap-year bug
 const daysFrom1970 = date.getTime() / 1000 / 3600 / 24;
 const daysFrom1900 = daysFrom1970 + daysIn70years;
 const daysFrom1904Jan2nd = daysFrom1900 - daysIn4Years - 1;
 return Math.round(date1904 ? daysFrom1904Jan2nd : daysFrom1900);
}
</code></pre>

If you want to know how this works check: <a href="https://bettersolutions.com/excel/dates-times/1904-date-system.htm" rel="nofollow noreferrer">https://bettersolutions.com/excel/dates-times/1904-date-system.htm</a>

blocks|key|330022|text|我真的很喜欢Gil的回答，因为它很简单，但它没有时区偏移。所以，它是这样的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|330023|function+date2ms(d)+{
++let+date+=+new+Date(Math.round((d+-+25569)+*+864e5));
++date.setMinutes(date.getMinutes()+%2B+date.getTimezoneOffset());
++return+date;
}|code-block|syntax|javascript|330024|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I really liked Gil's answer for it's simplicity, but it lacked the timezone offset. So, here it is:
<pre><code>function date2ms(d) {
 let date = new Date(Math.round((d - 25569) * 864e5));
 date.setMinutes(date.getMinutes() + date.getTimezoneOffset());
 return date;
}
</code></pre>

blocks|key|112610|text|感谢@silkfire的解决方案！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|112611|在我核实之后。我发现当你在东半球时，@silkfire有正确的答案；西半球则相反。|112612|因此，要处理时区，请参见下面的内容：|112613|function+ExcelDateToJSDate(serial)+{
+++//+Deal+with+time+zone
+++var+step+=+new+Date().getTimezoneOffset()+<=+0+?+25567+%2B+2+:+25567+%2B+1;
+++var+utc_days++=+Math.floor(serial+-+step);
+++var+utc_value+=+utc_days+*+86400;++++++++++++++++++++++++++++++++++++++++
+++var+date_info+=+new+Date(utc_value+*+1000);

+++var+fractional_day+=+serial+-+Math.floor(serial)+%2B+0.0000001;

+++var+total_seconds+=+Math.floor(86400+*+fractional_day);

+++var+seconds+=+total_seconds+%25+60;

+++total_seconds+-=+seconds;

+++var+hours+=+Math.floor(total_seconds+/+(60+*+60));
+++var+minutes+=+Math.floor(total_seconds+/+60)+%25+60;

+++return+new+Date(date_info.getFullYear(),+date_info.getMonth(),+date_info.getDate(),+hours,+minutes,+seconds);
}|code-block|syntax|javascript|112614|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Thanks for @silkfire's solution! 
After my verification. I found that when you're in the Eastern Hemisphere, @silkfire has the right answer; The western hemisphere is the opposite. 
So, to deal with the time zone, see below:
<pre><code>function ExcelDateToJSDate(serial) {
 // Deal with time zone
 var step = new Date().getTimezoneOffset() &lt;= 0 ? 25567 + 2 : 25567 + 1;
 var utc_days = Math.floor(serial - step);
 var utc_value = utc_days * 86400; 
 var date_info = new Date(utc_value * 1000);

 var fractional_day = serial - Math.floor(serial) + 0.0000001;

 var total_seconds = Math.floor(86400 * fractional_day);

 var seconds = total_seconds % 60;

 total_seconds -= seconds;

 var hours = Math.floor(total_seconds / (60 * 60));
 var minutes = Math.floor(total_seconds / 60) % 60;

 return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
}
</code></pre>

I have the following javascript code that convert date (string) to the Date Serial Number used in Microsoft Excel:

<pre><code>function JSDateToExcelDate(inDate) {

 var returnDateTime = 25569.0 + ((inDate.getTime() - (inDate.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
 return returnDateTime.toString().substr(0,5);

}
</code></pre>

So, how do I do the reverse? (Meaning that a Javascript code that convert the Date Serial Number used in Microsoft Excel to a date string?

Converting Excel Date Serial Number to Date using Javascript

Unix

我有以下javascript代码，可以将日期(字符串)转换为Microsoft Excel中使用的日期序列号：function JSDateToExcelDate(inDate) {    var returnDateTime = 25569.0 + ((inDate.getTime() - (inDate.getTi...

问使用Javascript将Excel日期序列号转换为日期
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Javascript将Excel日期序列号转换为日期EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Javascript将Excel日期序列号转换为日期
EN