New to Telerik UI for ASP.NET Core? Download free 30-day trial

Converting Spreadsheet Dates to Javascript

Environment

Product Telerik UI for ASP.NET Core Soreadsheet
Progress Telerik UI for ASP.NET Core version Created with the 2023.1.314 version

Description

I access a range of cells in my Spreadsheet and utilize the value method of the Range's API to extract the date values of the range. However, I get numbers that are not usable in JavaScript. How can I format these dates to JavaScript?

Solution

Date values in the Spreadsheet are converted to numbers internally to maintain compatibility with the specifications of Microsoft Excel's date formatting.

To convert the formatted dates back to JavaScript, pass the date value to the following getJsDatesFromExcel function:

    function getJsDateFromExcel(excelDate){
        const SECONDS_IN_DAY = 24 * 60 * 60;
        const MISSING_LEAP_YEAR_DAY = SECONDS_IN_DAY * 1000;
        const MAGIC_NUMBER_OF_DAYS = (25567 + 2);    
        if (!Number(excelDate)) {
            alert('wrong input format')
        }

        const delta = excelDate - MAGIC_NUMBER_OF_DAYS;
        const parsed = delta * MISSING_LEAP_YEAR_DAY;
        const date = new Date(parsed)

        return date
    }

    var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
    var sheet = spreadsheet.activeSheet();
    var range = sheet.range("B4");
    var value = range.value();

    var jsValue = getJsDateFromExcel(value);

To explore the complete behavior, see the Telerik REPL example on how to format Excel dates to JavaScript Date objects.

More ASP.NET Core Spreadsheet Resources

See Also

In this article