Excel Export is not respecting timestamps due to incorrect rounding.

I was attempting to use Excel export feature and ran into a bug that I'm surprised hasn't been brought up before or if it has, I wasn't able to find it with a search.

I have set up a snippet here: https://snippet.webix.com/3a5zq64c

based on the example here: https://snippet.webix.com/hbg9f7ty

The problem is when you specify that you want to display a timestamp and then try to output it to Excel using toExcel and using the custom template as I have specified in my example, it is rounding the timestamp off to the nearest day. I couldn't figure out why because using SheetJS in other projects, I have used timestamps just fine. I dug into the source code and there is a function defined:
function excelDate(date) {
return Math.round(25569 + date / (24 * 60 * 60 * 1000));
}

Math.round is a problem here because take this example:
(0 + +(new Date())/86400000) + 25569
As of today, this should return something like 43499.85740354167 but with rounding, it returns 43500. Not only does that not respect the timestamp, it returns the wrong day completely. When you plug these numbers into Excel and set the format it gives the following output:

43499.857 = 2/3/2019 8:34:05 PM
43500 = 2/4/19 12:00 AM

I can't use the Export feature at all due to this bug. Is there a work around? Can I redefine excelDate somehow?

Comments

  • Also, the more significant digits you round off of the end, the less accurate the timestamp gets.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion