1

I am working on importing data in an Excel file and it has date column. In my app, that date column value comes as a serial number like 43101.622083333335 which stands for 01/01/2018.

When converting this serial number from Excel back to the normal date it stands for, it gives wrong year. For example, it gives 01-Jan-1970 instead of 01-Jan-2018 ``

var moment = require('moment');
var excelDate = 43101.622083333335;
var date = moment(new Date(excelDate));
var dateWithNewFormat = date.format('DD-MMM-YYYY');
console.log(dateWithNewFormat);

``

Output: 01-Jan-1970 instead of 01-Jan-2018

Any help ? Thanks in advance.

CCC
  • 111
  • 5
  • 16

1 Answers1

1

I don't think this is an issue with the moment library. It seems that you aren't calling Date with a valid constructor argument with new Date(excelDate) (see official documentation for Date here).

The Date class doesn't understand the concept of 'Excel time' but it does understand the concept of a unix timestamp. If you refer to this post, you can see how to convert from Excel time to a unix timestamp, depending on which version of Excel you are using.

Then, I would change your code to:

var moment = require('moment');
var excelDate = 43101.622083333335;
var unixTimestamp = (excelDate-25569)*86400 //as per the post above, convert Excel date to unix timestamp, assuming Mac/Windows Excel 2011 onwards
var date = moment(new Date(unixTimestamp)); //Pass in unix timestamp instead of Excel date
var dateWithNewFormat = date.format('DD-MMM-YYYY');
console.log(dateWithNewFormat);
caladeve
  • 456
  • 4
  • 12
  • 1
    thanks for helping but i updated your answer with the help of this post, https://stackoverflow.com/questions/20943089/how-to-convert-unix-timestamp-to-calendar-date-moment-js to get the required output. – CCC Jul 23 '19 at 08:52
  • 2
    The `var unixTimestamp = (excelDate-25569)*86400` line should really be `var unixTimestamp = (excelDate-25569)*86400000` -- the offset value needs to be multiplied by milliseconds per hour. – Allen Luce Mar 30 '22 at 04:28