1. Basics1. The date in Excel is calculated from 1900-1-0, so 1900-1-1 is 1 day; 2. The js Date starts at 1970-1-1 08:00:00; The time conversion in Excel is as follows: Click General and the changes are as follows: 2. Problem DescriptionOften when we import data into Excel, a digital time is parsed. In this case, it is necessary to perform time formatting conversion! 3. Solution1. Subtract 1900-1-1 from 1970-1-1 to get the difference: 25567 days, 0 hours, 5 minutes and 43 seconds; 2. Subtract the extra 1 day and 8 hours; The js code is as follows: let time = new Date((43831-25567) * 24 * 3600000 - 5 * 60 * 1000 - 43 * 1000 - 24 * 3600000 - 8 * 3600000) let year = time.getFullYear() + '' console.log('year:'+year) let month = time.getMonth() + 1 + '' console.log('month:'+month) let date = time.getDate() + '' console.log('data:'+date) Appendix: Issues with converting date formats in excel using jsWhen using the js-xlsx plug-in to read Excel, data such as 2018/10/16 will be automatically converted to 48264.12584511. So you need to convert it back manually. // When Excel reads the time format of 2018/01/01, it will convert it into a number similar to 46254.1545151415. numb is the integer number passed in, and format is the symbol for the interval formatDate(numb, format) { const time = new Date((numb - 1) * 24 * 3600000 + 1) time.setYear(time.getFullYear() - 70) const year = time.getFullYear() + '' const month = time.getMonth() + 1 + '' const date = time.getDate() - 1 + '' if (format && format.length === 1) { return year + format + month + format + date } return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date) }, console.log(formatDate(42618, '/')) // 2016-9-5 SummarizeThis is the end of this article about the correct way to convert time in js when importing excel. For more relevant content about converting time in js when importing excel, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future! You may also be interested in:
|
<<: An article to master MySQL index query optimization skills
>>: Detailed explanation of the use of stat function and stat command in Linux
Table of contents JavaScript Objects 1. Definitio...
Table of contents 1. Introduction 2. Code Impleme...
The MySQL slow query log is very useful for track...
time(); function Function prototype: time_t time(...
When using MySQL, many developers often perform f...
Disable swap If the server is running a database ...
Table of contents Pitfalls encountered in timesta...
I started configuring various environments this a...
1. Nginx status monitoring Nginx provides a built...
I have been working on a project recently - Budou ...
This article shares the specific code of JavaScri...
In some cases, the data in data needs to be reuse...
Preface In project development, there are many wa...
Table of contents Reactive Function usage: toRef ...
In the previous article, we talked about MySQL tr...