Converting Excel date/time to Unix timestamp

I was working on excel sheet reports other day, and i came across an inetersting problem. I was using Excel reader class to read the excel file, which wa containing one column of date. While reading that column value, I found that, it contains some numeric value which was not unix timestamp value. I was puzzeled for some time.

After some searching and surfing on netty with the help of developer's biggest help Mr . Google, I came to know that Windows and Unix system behave diferrently to store the time stamp. I found the same question was asked in one of google groups, so finally i got the reason to put this on my blog.

In Unix system timestamp is stored as integer in terms of seconds. If we consider the current timestamp value in Unix, it is the no. of seconds passed from 1 January 1970 00.00Hrs till now. One important thing is, Unix timestamp is measured with reference to GMT time. That means there is no time-zone adjustment considered in case of Unix timestamp.

In case of Windows time it is stored as floating point/real number but in terms of days. For current timestamp, the no. days spent from 1 January 1900 stored in integer part and fraction of the day stored in the fractional part of the timestamp.

Once this was cleared to me, it was rather easy to go further.

Here are the conversion calculations

$intDayDifference = 25569; //Day difference between 1 January 1900 to 1 January 1970

$intDay2Seconds = 86400; // no. of seconds in a day

$intExcelTime //integer value stored in the Excel column

$intUnixTime = ($intExcelTime - $intDayDifference)*$intDay2Seconds;

Even after doing all this, you may find the calculations are not absolutely accurate, and that is because fractional part of the Excel time. The reason behind doing so is that all I need to do is to convert a date from Windows format to Unix format. So fraction part of windows time won’t affect a lot for my requirement. But if you use this calculation for converting a date-time from Windows format to Unix format then conversion of the fractional part should done.

I hope this would help you to convert timestamp between unix and windows. If you have any comments or questions feel free to ask. You can even contact me at ninad.blog@gmail.com

Thanks.

Setting innerHTML gives error in IE

innerHTML ::


The innerHTML property reflects the content contained by the referenced element, including all the HTML elements.

For example, consider the following <DIV> element:

<DIV ID="Address1">

Shiv Prasad,<BR>

1, Rajendra Babu Road,<BR>

Wadala,<BR>

Mumbai, India

</DIV>

The address' innerHTML property (obtained by referencing it via its ID property) would be:

Shiv Prasad,<BR>
1, Rajendra Babu Road,<BR>
Wadala,<BR>
Mumbai, India .

The innerHTML property has read-write permissions, meaning that HTML content can be 'written' into any element through scripting, with the document being re-displayed accordingly, dynamically re-positioning surrounding document content. The new HTML content would adopt whatever stylings (and ID) were applied to the referenced element, before the new content was supplied.

However innerHTML property works for all objects except the following, for which it is read-only: COL, COLGROUP, FRAMESET, HEAD, HTML, STYLE, TABLE, TBODY, TFOOT, THEAD, TITLE, TR. I checked with couple of browsers, and found IE is the most problematic. IE gives error and halts the execution of javascript if you try to set innerHTML property for above said elements. Works fine with mozilla firefox & safari though. This is one more reason why Microsoft should look for the products with better quality.