[Solved] Wrong number of days in a date column?

[Reasult]

I have a spreadsheet with a date column. When I enter 1/1/2018 the cell returns 43101. This must be the number of days from 1/1/1900.

When I calculate the entered date from this number of days the result is 1/3/2018. I use this function:

 

var days = 43101
var date = new Date(1900, 0, 1);
date.setDate(date.getDate() + days);

 

Here you can find an example: http://dojo.telerik.com/aNuXAkuB

The Days function in Excel returns 43100 for the number of days between 1/1/1900 and 1/1/2018.

 

[Reasult]

Or is there a function in Kendo UI to return the actual date from a spreadsheet cell instead of the number of days?

[Veselin Tsvetanov]

Hello Pierre,

Thank you for the detailed description and for the sample prepared.

As per the date calculation issue, I noticed, that the number of days for 1/1/2018 (43101) is added to the first day (1/1/1900 or 1). This would result in total value of 43102 days (43101 + 1). In addition to this, Excel has a bug, which was deliberately introduced in the program with its initial development. It calculates the year 1900 as a leap year adding unexciting day to it (29/02/1900). As in the browser such date does not exist we had to workaround the Excel bug.

In order to use the same number value in Spreadsheet as in Excel for each date after 29/02/1900, we moved the starting date from 1/1/1900 to 31/12/1899. This means that all dates after 29/02/1900 will have the same number value in both the Kendo Spreadsheet and Excel, but the dates before that will be shifted by 1. Having that said, in order to get the proper date in the Dojo sample sent, you will need to alter it in the following way:

// In the Kendo Spreadsheet, the first day is 31/12/1899
// Therefore, the zero (0) date would be 30/12/1899
var date = new Date(1899, 11, 30);
date.setDate(date.getDate() + days);

Here you could find a modified version of the Dojo sample sent.

As per the date function question, the Spreadsheet widget does not expose such feature. It will always return the date as a number. Nevertheless, you could follow the above explained logic to properly calculate the date.

Regards,
Veselin Tsvetanov
Progress Telerik

[Reasult]

Thanks @Veselin for you answer. It's now working!!