This is a migrated thread and some comments may be shown as answers.

Wrong number of days in a date column?

3 Answers 192 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Reasult
Top achievements
Rank 1
Iron
Reasult asked on 06 Apr 2018, 11:56 AM

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.

 

3 Answers, 1 is accepted

Sort by
0
Reasult
Top achievements
Rank 1
Iron
answered on 06 Apr 2018, 12:27 PM
Or is there a function in Kendo UI to return the actual date from a spreadsheet cell instead of the number of days?
0
Accepted
Veselin Tsvetanov
Telerik team
answered on 10 Apr 2018, 08:04 AM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Reasult
Top achievements
Rank 1
Iron
answered on 10 Apr 2018, 11:10 AM
Thanks @Veselin for you answer. It's now working!!
Tags
Spreadsheet
Asked by
Reasult
Top achievements
Rank 1
Iron
Answers by
Reasult
Top achievements
Rank 1
Iron
Veselin Tsvetanov
Telerik team
Share this question
or