I recently needed to post some data from a web page into a database table. This database table happens to be viewable as a simple html table on a web page. As part of the analysis of the data, the html table of data is inserted into an Excel worksheet as a Web Query. One of the items of data I needed to submit was the date and time at which the data was submitted. I wanted to post this information as an Excel date / time “serial value”, so that the Web Query could format the date and time within Excel without any further manipulation. This article contains the JavaScript function I created to format a JavaScript date as an Excel date / time serial value.

I have used this code when working with Excel 2003 on Windows XP, but it should work with any version of Excel which supports Web Queries.

Here’s the function (where inDate is a JavaScript date object):


function JSDateToExcelDate(inDate) {

var returnDateTime = 25569.0 + ((inDate.getTime() – (inDate.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
return returnDateTime.toString().substr(0,20);

}


An example of this code in use:


var nowDate = new Date();
alert(JSDateToExcelDate(nowDate));