Date formatted columns lose value in JSON?

[RobBrennan]

Hi there! Is there a way to read in data from an Excel sheet so that it retains the date? When I try to read in date-formatted columns (such as 11/4/14) it gives it a value of 41947 in the JSON object.

[snypelife]

I've noticed this as well. Almost like it's trying to perform an arithmetic operation on the value.

[snypelife]

Did some digging and it appears it's due to the xlsx module that this one depends on:
SheetJS/sheetjs#126

Basically, the number being return is the number of days since the last epoch (1904 or 1900).

[snypelife]

so you may be able to do something like:

var date = new Date(1900, 0, dateFromEpoch)

[shyamseshadri]

Do note, just encountered this. Excel has a leap year bug (http://support.microsoft.com/kb/214326) which causes the dates to be off by one if you do @snypelife's suggestion.

So you will need to do an additional -1

var date = new Date(1900, 0, dateVal - 1);

[uncedric]

@shyamseshadri so... this could work?

var date = new Date(1899, 0, dateVal );

[snypelife]

@uncedric little bit late of a reply, so probably don't need an answer anymore, but yes that would work.

[snypelife]

actually, it would have to be var date = new Date(1899, 11, dateValue); i believe.

[pranaysaha]

var date = new Date(1899, 0, dataVal - 1);
otherwise in previous cases either it will give +1 day or -1 day.
Eg. 2016-06-01 was my date and after parsing it is showing 42522 days, so in following cases it showed:

1. var date = new Date(1900, 0, dateVal - 1); 2016-05-31
2. var date = new Date(1899, 0, dateVal - 1 ); 2015-06-01
3. var date = new Date(1899, 0, dateVal); 2015-06-02
   so I used following:

var date = new Date(1899, 12, dateVal ); 2016-06-01

[Vincentliu89]

10:00:00 will be converted to 9:59:59.

[ericzhao007]

@Vincentliu89 I met the same situation,i need help

[BumbuKhan]

Date in my xlsx file was 01.03.2018, to get correct one after parsing I did:

const correctDate = new Date(1899, 12, parsedDate - 1)

then to format it properly I used moment.js lib so finally:

const startDate = moment(new Date(1899, 12, opportunity[2] - 1)).format('YYYY-MM-DD');

[pranaysaha]

@Vincentliu89 @zhaoxinwei I need more clarity on your use case. May be u can explain me the date and time format you are storing in excel sheet.
And @BumbuKhan why do you need to do so much? I have tried your date with my solution it is coming perfect date.No problem this. If you are facing some issue please elaborate little bit with your use case.
Thanks

[vsnig]

To fix this "10:00:00 will be converted to 9:59:59"
Do
const date = new Date(1899, 12, dateVal - 0.99999999 );

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[EvanCarroll]

I'm still having this issue.

[theromie]

Use this
return new Date ((excelDate - 25567 - 2) * 86400 * 1000);

[longhaoxuan]

Use this
return new Date ((excelDate - 25567 - 2) * 86400 * 1000);

utc = new Date();
new Date((excelDate - 25567 - 2) * 86400 * 1000 - utc.getTimezoneOffset*1000*60);

how to optimize

[yzh1211]

hi,i got a float value from Excel like '367.084722222222',how can i convert it to Date in javascript?

[zhangchenle666]

The following code supports csv, xls, xlsx：
1.Read file like this

// suffix: file extension
// buffer: use fs.readFile()

if (suffix === 'csv') {
const detact = jschardet.detect(buffer.slice(0, 100))
if (detact.encoding !== 'UTF-8') {
const str = iconv.decode(buffer, detact.encoding)
buffer = iconv.encode(str, 'UTF-8')

// add utf8 BOM Header(3bytes)
const bomBuffer = Buffer.alloc(3)
bomBuffer.fill(239, 0, 1)
bomBuffer.fill(187, 1, 2)
bomBuffer.fill(191, 2, 3)
buffer = Buffer.concat([bomBuffer, buffer], buffer.length + 3)
}
}
const workbook = xlsx.parse(buffer, {raw: true, cellDates: true})

2.and forEach all columns:
if (value instanceof Date)

3.if xlsx:
value = new Date(value.getTime() + 43 * 1000 + 1) // 43seconds an 1 millseconds
then:
const year = value.getFullYear()
let month = value.getMonth() + 1
if (month < 10) month = 0 + month
let date = value.getDate()
if (date < 10) date = '0' + {date}
let hours = value.getHours()
if (hours < 10) hours = '0' + hours
let minutes = value.getMinutes()
if (minutes < 10) minutes = '0' + minutes
let seconds = value.getSeconds()
if (seconds < 10) seconds = '0' + seconds
value = ${year}-${month}-${date} ${hours}:${minutes}:${seconds}

[liderdong]

用这个
return new Date ((excelDate - 25567 - 2) * 86400 * 1000);

May I ask why I have used it for 8 days longer? Is there any solution?

[timohausmann]

The solutions by theromie, BumbuKhan and shyamseshadri work back to around the year 1600. Sadly, when you have historic data that's older, it gets inaccurate. Time is an issue on it's own. Tests: https://jsfiddle.net/cehu8wgt/

[shangzm]

Use this return new Date ((excelDate - 25567 - 2) * 86400 * 1000);

why?

[SheetJSDev]

Upstream tracking issue SheetJS/sheetjs#1565 . A part of the overall date issues stem from a V8JS (Chrome / NodeJS JavaScript engine) bug which can be tracked at https://bugs.chromium.org/p/v8/issues/detail?id=7863

FWIW none of the simple datecode translations are correct around DST dates like a few days ago (Nov 7 was rollback day for Eastern Time). JS engines perceived the logical day to have 90000 seconds while Excel treats every day as if it has 86400 seconds.

PS @timohausmann in the 1900 date system (most common), the smallest valid datecode is 0. Excel understands this to be "1900-01-00" which is commonly understood by humans to be the midnight of December 31 1899. A number of timezones including Asia/Hong_Kong and Europe/Paris aligned to a v8-friendly timezone after 1900. Depending on your timezone, you will find different dates which "work"

[ArvindhMangoleap]

I am having the same issue my excel has 21-Dec-2021 but the returning json shows 44551.
Is there any option that we can pass to sheet_to_json function so that it returns as 21-Dec-2021 without any formating?