Wrong number of days in a date column?

4 posts, 1 answers
  1. Reasult
    Reasult avatar
    4 posts
    Member since:
    Jan 2017

    Posted 06 Apr 2018 Link to this post

    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.

     

  2.
  3. Reasult
    Reasult avatar
    4 posts
    Member since:
    Jan 2017

    Posted 06 Apr 2018 in reply to Reasult Link to this post

    Or is there a function in Kendo UI to return the actual date from a spreadsheet cell instead of the number of days?
  5. Answer
    Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    1220 posts

    Posted 10 Apr 2018 Link to this post

    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.
  7. Reasult
    Reasult avatar
    4 posts
    Member since:
    Jan 2017

    Posted 10 Apr 2018 Link to this post

    Thanks @Veselin for you answer. It's now working!!
  8.
Back to Top