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

JavaScript Objects (details)

Table of contents JavaScript Objects 1. Definitio...

Vue data two-way binding implementation method

Table of contents 1. Introduction 2. Code Impleme...

How to enable the slow query log function in MySQL

The MySQL slow query log is very useful for track...

How to get the current time using time(NULL) function and localtime() in Linux

time(); function Function prototype: time_t time(...

MySQL functional index optimization solution

When using MySQL, many developers often perform f...

Some suggestions for Linux system optimization (kernel optimization)

Disable swap If the server is running a database ...

Pitfalls and solutions encountered in MySQL timestamp comparison query

Table of contents Pitfalls encountered in timesta...

Detailed explanation of Nginx status monitoring and log analysis

1. Nginx status monitoring Nginx provides a built...

iFrame is a great way to use it as a popup layer to cover the background

I have been working on a project recently - Budou ...

Implementing a shopping cart with native JavaScript

This article shares the specific code of JavaScri...

Vue resets data to its initial state

In some cases, the data in data needs to be reuse...

Introducing icons by implementing custom components based on Vue

Preface In project development, there are many wa...

Introduction to reactive function toRef function ref function in Vue3

Table of contents Reactive Function usage: toRef ...

MySQL storage engine basics

In the previous article, we talked about MySQL tr...