Converting Spreadsheet Dates to Javascript
Environment
| Product | Telerik UI for ASP.NET MVC Soreadsheet |
| Progress Telerik UI for ASP.NET MVC 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.