Question

196

Converting Excel Date Serial Number to Date using Javascript

rated 0 times [ 198] [ 2] / answers: 1 / hits: 63533 / 11 Years ago, fri, april 26, 2013, 12:00:00

I have the following javascript code that convert date (string) to the Date Serial Number used in Microsoft Excel:

function JSDateToExcelDate(inDate) {



    var returnDateTime = 25569.0 + ((inDate.getTime() - (inDate.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));

    return returnDateTime.toString().substr(0,5);



}

So, how do I do the reverse? (Meaning that a Javascript code that convert the Date Serial Number used in Microsoft Excel to a date string?

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

amari

Add To Favorites

Follow

Total Points: 736

Total Questions: 111

Total Answers: 90

Location: Saint Pierre and Miquelon

Member since Fri, Jan 28, 2022

2 Years ago

amari questions

1 Do SubmitEvents made by a 'submit' event contain the form values?

Mon, May 23, 22, 00:00, 2 Years ago

1 ERR_UNHANDLED_REJECTION UnhandledPromiseRejection when awaited inside an array of promise Objects

Mon, Jun 21, 21, 00:00, 3 Years ago

1 Dropdown with Search Box using Bootstrap Selectpicker not working

Thu, Jun 3, 21, 00:00, 3 Years ago

1 Redux-Toolkit: How to reject a createAsyncThunk?

Sat, May 15, 21, 00:00, 3 Years ago

1 Webpack, optimization chunking gives Conflict: Multiple chunks emit assets to the same filename error

Wed, Mar 3, 21, 00:00, 3 Years ago

View All

answered 11 Years ago collinarnoldp · Accepted Answer

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