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
Preface I am a PHP programmer who started out as ...
Preface Through my previous Tomcat series of arti...
Let's first look at the definition of the pos...
Using provide+inject combination in Vue First you...
In the process of product design, designers always...
Table of contents accomplish: Summarize: Not much...
In the project (nodejs), multiple data need to be...
Table of contents Preface Introduction-Official E...
Good morning everyone, I haven’t updated my artic...
This article shares the MySQL installation and co...
History always repeats itself surprisingly well. ...
Preface The logical judgment statements we use in...
Setting up remote access in mysql5.7 is not like ...
Definition of Float Sets the element out of the n...
Solve the problem that the responseText returned ...