CODEWITHSUNDEEP
node.jsdatedatetimetimezoneexceljs
Johnson Anthony
Johnson Anthony

Reputation: 235

Reading date in excel file through ExcelJS give wrong date

I am working at EST timezone. When I reading a date in Excel file with the help of ExcelJS. The date in Excel is 3/31/2021. But the same date is retrieving as Tue Mar 30 2021 20:00:00 GMT-0400 (Eastern Daylight Time). So it is considering the given date as UTC and converting it to my current timezone. I dont want to convert it to UTC and then to my current timezone. I want to read the direct date which is present in the EXCEL file.

I see dateUTC: false somewhere in the document. This is used at Writing the file. But how to do it when I am reading the excel file. Any help on this?

Upvotes: 1

Views: 4191

Answers (2)

Bekhx
Bekhx

Reputation: 11

First I converted the GMT date to milliseconds using Date.parse() and added the time difference between GMT and the current time zone (in my case Asia / Tashkent +5) to the resulting value, then converted it back to a regular date using new Date(milliseconds)

let m = Date.parse(gmtDate); //GMT date in milliseconds

let mTZ = m + 18000000; //Asia/Tashkent +5 time zone. 18.000.000 = 5 hours in milliseconds.

let currentDate = new Date(mTZ);

Upvotes: 1

clay
clay

Reputation: 6017

I ran into this recently and had to do the same thing:

  1. Read from excel as UTC
  2. then convert into the timezone I wanted for data

I know from your question you don't want to do this, so perhaps this plan is something to fall back on.

My code was something like this:

const moment = require('moment-timezone')
let utc = moment.utc(excelCellStr, 'M/D/YY hh:mm A')
let t = moment().tz('America/New_York')
t.year(utc.year())
t.month(utc.month())
t.date(utc.date())
t.hour(utc.hour())
t.minute(utc.minute())
t.second(utc.second())
excelCellStr = t.format()

I am not sure if there is a setting to avoid this or not. We are using exceljs as well.

Upvotes: 0

Related Questions