How to convert Excel Hour Serial Number to the H:M format?-CodePudding

Inside of Excel file, I have an hour:minute time format, example of one value from that excel file is: "19:15"

After uploading that file to my application, it is being read as "0.8020833333333334".

I want to convert that value from column ("0.8020833333333334") to get hours and minutes.

Here's what I have accomplished so far:

const extractTime = new Date(Math.round((0.8020833333333334 - 25569) * 864e5));

The result is not what I expected.

How can I convert this number to get the hour as 19, and minutes as 15?

CodePudding user response：

I've been "digging" around StackOverflow's questions about this, and my answer is based on this question's answers, as well as ECMA's date time. Mostly my answer is based on this answer.

In the comments you can see, that there will be problems if you're working with date-time values, based on this comment:

This anwer is wrong for 2 reasons: 1) the final Date constructor in the return value will create the time as a local time - results will differ depending on the client. 2) the solution does not take the 1900 leap year bug into account. I believe my solutions solves both issues. https://stackoverflow.com/a/57184486/12826055

And in case someone is working with date-time format, I would suggest using the answer which is linked inside of quotes, but in the case you only have TIME (19:30 -> 0.8020833333333334) and similar, it will be correct to use the code below.

Code:

var serial = 0.8020833333333334
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;

console.log(hours)
console.log(minutes)

Outputs:

19
15