Converting Excel Date Serial Number to Date Using JavaScript

Converting Excel Date Serial Number to Date using Javascript

Try this:

function ExcelDateToJSDate(serial) {
   var utc_days  = Math.floor(serial - 25569);
   var utc_value = utc_days * 86400;                                        
   var date_info = new Date(utc_value * 1000);

   var fractional_day = serial - Math.floor(serial) + 0.0000001;

   var total_seconds = Math.floor(86400 * fractional_day);

   var seconds = total_seconds % 60;

   total_seconds -= seconds;

   var hours = Math.floor(total_seconds / (60 * 60));
   var minutes = Math.floor(total_seconds / 60) % 60;

   return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
}

Custom made for you :)

Converting Excel Date Serial Number to Date using JavaScript in All timezone

Using the below function we can convert excel date to javascript date in all timezone.

ExcelDateToJSDate(serial) {
                 var hours = Math.floor((serial % 1) * 24);
                 var minutes = Math.floor((((serial % 1) * 24) - hours) * 60)
                 return new Date(Date.UTC(0, 0, serial, hours-17, minutes));
        }

Convert Javascript Date object to Excel serial date number

finally I was able to convert it properly, I used the following code to do so:

let date = new Date();
let converted = 25569.0 + ((date.getTime() - (date.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));

Thank you all.

Convert excel DATEVALUE to javascript date

//Convert Excel dates into JS date objects

//@param excelDate {Number}
//@return {Date}

function getJsDateFromExcel(excelDate) {

 // JavaScript dates can be constructed by passing milliseconds
 // since the Unix epoch (January 1, 1970) example: new Date(12312512312);

 // 1. Subtract number of days between Jan 1, 1900 and Jan 1, 1970, plus 1  (Google "excel leap year bug")             
// 2. Convert to milliseconds.

 return new Date((excelDate - (25567 + 1))*86400*1000);

 }

Javascript convert short date number to javascript date

Excel stores dates internally using OLE Automation Date (OADate).

The base OLE Automation Date is midnight, 30 December 1899. OLE Automation date is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24. [https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=netcore-3.1#remarks]

You need to subtract 25,569, which is the number of days between 30 December 1899 and 1 January 1970, before converting to milliseconds.

To convert the date:

var excelDate = 43527;
var parsedDate = new Date((excelDate - 25569) * 86400 * 1000)).toISOString();

As mentioned here Converting Excel Date Serial Number to Date using Javascript

Converting Excel date to Moment date gives a wrong year output

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);

How to convert a date in columns to date serial number in Excel?

Assuming your data start in the first row

=DATE(A1,B1,C1)+TIME(D1,E1,0)

Excel - Convert JavaScript/Unix timestamp to date

Excel is pretty happy to interconvert between dates and numbers, as you noticed. However, afaict, it can't always guess correctly which of the two you want to see.

So, to ensure that a value is rendered as a date, you'd need to open the Format Cells dialog, go to the first tab Number, and set the cell's format Category to one of the Date types.

---

Related Topics