So I have a Javascript date object with year, month, days, hours, minutes, seconds, and milliseconds data and I need to convert this object to an Excel serial number but I didn't find a way to do that. I found how to convert only a Date object but the way I found didn't consider the time part.
Any suggestions? Thank you, Regards.
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));
Where 25569 is the difference between Excel start date (1900/01/01) and Javascript start date (1970/01/01).
Thank you all.
My versions of João Durante's code. I had trouble with timezone offset calculation that caused the float value to stack with time, thus switched to using straightforward UTC. Also, dates in the year 1900 weren't calculated properly. This code was tested with start and end dates of months in 1900, 2000, 2010, and maximum date value for Excel (9999-12-31)
function JSDateToExcelDate(year, month, day) {
let date = new Date(Date.UTC(year, month - 1, day));
let ExcelSerialNumber = 25569.0 + (date.getTime() / (1000 * 60 * 60 * 24));
if (year == 1900 && month == 2 && day == 29) {
return 60
}
if (ExcelSerialNumber <= 60) {
return ExcelSerialNumber-1;
}
return ExcelSerialNumber;
}
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);
}
Or you can use:
function SerialDateToJSDate(serialDate) {
var days = Math.floor(serialDate);
var hours = Math.floor((serialDate % 1) * 24);
var minutes = Math.floor((((serialDate % 1) * 24) - hours) * 60)
return new Date(Date.UTC(0, 0, serialDate, hours-17, minutes));
}
hours - 17 is coming from?