Convert Excel Date string to Unix time in Node.js

Question

I use node-xlsx for parsing Excel file in Node.js but Date cells have strange view before parsing:

in Excel: 01.03.2016 07:44:04
in Node.js before parsing: 42430.32226851852

How I can convert this string 42430.32226851852 to Unix-time format?

Update: Write myself solution that work for me:

var xlsxDate = '42430.32226851852'
var splitDate=xlsxDate.split('.');
var unixTime=new Date(1900, 0, splitDate[0]-1, 0, 0, Math.round(splitDate[1]/1157410)).getTime()/1000
console.log(unixTime)

Where 1157410 is ~1 second

Hello Dan. I think this link can help you stackoverflow.com/questions/42183859/… — Ediruth
– Ediruth, Commented Oct 18, 2017 at 11:48
Thanks but not shure. I find information that dot point split 42430.32226851852 to date and time: 42430 - date (from 01.01.1900), 32226851852 - time. And now I dont understend how I can transform 32226851852 to normal time format. Excel is rounded 32226851852 to 32227 when i change cell format from Date to String — Dan Mishin
– Dan Mishin, Commented Oct 18, 2017 at 12:46

Mohammad Nazari · Accepted Answer · 2019-11-29 06:47:42Z

1

i use this code to convert Unix-time format:

date = new Date(( parseInt(excelDate) - (25567 + 2))*86400*1000)
unix = date.getTime()

edited Nov 29, 2019 at 6:47

answered Nov 28, 2019 at 0:44

Mohammad Nazari

3,0952 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Webster · Accepted Answer · 2018-02-19 20:46:37Z

0

Another work around is that when you save it to your excel document you save it as a string and not a Date object.

Then you can read it as a string and just wrap it in a new Date()

answered Feb 19, 2018 at 20:46

David Webster

2,3431 gold badge18 silver badges27 bronze badges

Collectives™ on Stack Overflow

Convert Excel Date string to Unix time in Node.js

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related