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!