Question about cell value retrieved in date format using serialize()

Spreadsheet
#1

Using spreadsheet v5.2.
When the cell format is “date” and a date is entered, the cell value obtained when the serialize() method is executed contains a decimal instead of a date.
How can I change this to the entered date string?

ex)
When “2025-01-01” is entered in a cell in Date format, the cell value obtained by serialize() contains “45658.375”.

#2

Hello,

Thank you for reporting this behavior with date formatting in DHTMLX Spreadsheet v5.2.
By default, DHTMLX Spreadsheet stores dates in Excel-style serial number format (e.g., 45658.375). This format reflects the number of days since Excel’s base date (January 1, 1900), and is used for internal calculations. However, when calling the serialize() method or copying cell data, this numeric representation may appear instead of the originally entered date string (like 2025-01-01).

You can post-process cells formatted as “date” and translate their value back into a human-readable string (e.g., “2025-01-01”). Here’s a utility function that converts a numeric date into the ISO format (YYYY-MM-DD) and a format-checking helper:

function formatDateValue(value) {
    let date;
    if (typeof value === "number") {
        date = new Date((value - (25567 + 2)) * 86400 * 1000);
    } else if (value instanceof Date) {
        date = value;
    } else {
        return "";
    }
    return date.toISOString().split("T")[0]; 
}

function processCellValue(value, format) {
    if (format === "date" && (typeof value === "number" || value instanceof Date)) {
        return formatDateValue(value);
    }
    return value ?? "";
}

This line:

new Date((value - (25567 + 2)) * 86400 * 1000)
  • 25567 is the number of days between Excel’s base date (January 1, 1900) and JavaScript’s epoch (January 1, 1970).
  • + 2 compensates for Excel’s leap‑year bug in 1900 and indexing adjustments, ensuring accurate conversion.
  • Multiply by 86400 * 1000 converts the difference from days to milliseconds – the unit JavaScript Date expects.

To override the default copy behavior for cells with date values, you can listen for the copy event and conditionally format the output. Grab cell values with getValue() and formats with getFormat(), then build a string using processCellValue():

document.addEventListener("copy", (e) => {
    const targetCell = e.target.closest('.dhx-spreadsheet .dhx_grid-cell');
    if (!targetCell) {
        return;  // allow default behavior outside spreadsheet cells
    }

    const selectedCells = spreadsheet.selection.getSelectedCell();
    if (!selectedCells) return;
    console.log(selectedCells);

    const values = spreadsheet.getValue(selectedCells);
    const formats = spreadsheet.getFormat(selectedCells);
    const text = buildClipboardText(values, formats);

    e.clipboardData.setData("text/plain", text);
    e.preventDefault();
});

In this way, you intercept the default clipboard output only when the user copies from a spreadsheet cell. All other copy operations remain unaffected.

Please see the full working example: DHTMLX Snippet Tool.
Let us know if this helps or if you need further assistance.

Best regards,
Valeria Ivashkevich
Support Engineer

1 Like
#3

Thank you for your reply.
I was able to successfully convert the date cell value obtained with serialize() to a string.

1 Like