About the correct way to convert time in js when importing excel

About the correct way to convert time in js when importing excel

1. Basics

1. 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 Description

Often when we import data into Excel, a digital time is parsed. In this case, it is necessary to perform time formatting conversion!

3. Solution

1. 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 js

When 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

Summarize

This 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:
  • Conversion between js timestamp and date format
  • JavaScript timestamp and date string conversion code (super simple)
  • js time format and timestamp conversion example code
  • Time conversion in js - sample code for converting milliseconds to date and time
  • Detailed explanation of JavaScript UTC time conversion method
  • JS converts time seconds into a string of days, hours, minutes and seconds
  • JS obtains time related functions and conversion between timestamp and time date
  • Vue.js time conversion code and timestamp to time string
  • JavaScript script to convert local time to other time zones
  • js gets the time and implements the conversion between string and timestamp

<<:  An article to master MySQL index query optimization skills

>>:  Detailed explanation of the use of stat function and stat command in Linux

Recommend

MYSQL transaction tutorial Yii2.0 merchant withdrawal function

Preface I am a PHP programmer who started out as ...

Detailed explanation of how Tomcat implements asynchronous Servlet

Preface Through my previous Tomcat series of arti...

Vue implements partial refresh of the page (router-view page refresh)

Using provide+inject combination in Vue First you...

Analysis of product status in interactive design that cannot be ignored in design

In the process of product design, designers always...

HTML+CSS to achieve responsive card hover effect

Table of contents accomplish: Summarize: Not much...

How to insert batch data into MySQL database under Node.js

In the project (nodejs), multiple data need to be...

Guide to using env in vue cli

Table of contents Preface Introduction-Official E...

The best explanation of HTTPS

Good morning everyone, I haven’t updated my artic...

MySQL 5.7 installation and configuration tutorial

This article shares the MySQL installation and co...

How to optimize logic judgment code in JavaScript

Preface The logical judgment statements we use in...

mysql5.7 remote access settings

Setting up remote access in mysql5.7 is not like ...

About CSS floating and canceling floating

Definition of Float Sets the element out of the n...

Ajax responseText parses json data case study

Solve the problem that the responseText returned ...