how to handle date type in excel addin office.js

Question

i have a task-pane based add-in for Excel developed using office.js. i copy a date on one of the cell in the excel, and then read it next time from that cell. basically i use javascript Date object to convert the read string from excel to date and then use it.

I think this approach will create problem when excel file is saved in say English culture and open in on a machine which has french culture. because the format of the date is different in both culture.

i want to know how to handle this situation. is there any way to say the cell in Excel is of date type. and then it adjust its value accordingly in different culture.

Answer 1

If the value type of the cell is date, when we get the value from that text we would get a number instead of text display on the UI.

The number represents the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time. Refer to here about the dates and times in Excel. However, the date in JavaScript starts from 1 January, 1970 UTC( refer to Date in JavaScript).

We need to convert the number into the date time we wanted in JavaScript. And the Excel doesn’t compute the time zone when it convert the date time to value.

Here is a demo that convert the value of date in Excel to UTC for your reference:

function getJsDateFromExcel(excelDateValue) {

    return new Date((excelDateValue- (25567+2 )) * 86400 * 1000);
}

Date in Excel: 6/14/2016 12:00:00 PM

Value of the date: 42535.5

Convert the value to date in JavaScript: getJsDateFromExcel(42535.5).toUTCString()

Result: "Tue, 14 Jun 2016 12:00:00 GMT"

Update

What’s the date format in your code? We need to specify the correct format Excel could recognize. After you set the value, if the date is recognize as string it will align left instead of like figure below:

Here is the code to set/get the date:

function setData() {
        Excel.run(function (ctx) {
            var sheetName = "Sheet1";
            var rangeAddress = "A1";
            var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
            range.load("values");
            return ctx.sync().then(function () {
                range.values = "6/15/2016 13:00:00";
            });
        }).catch(function (error) {
            console.log("Error: " + error);
            if (error instanceof OfficeExtension.Error) {
                console.log("Debug info: " + JSON.stringify(error.debugInfo));
            }
        });
    }

function getData() {
    Excel.run(function (ctx) {
        var sheetName = "Sheet1";
        var rangeAddress = "A1";
        var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
        range.load("values");
        return ctx.sync().then(function () {

            var d = getJsDateFromExcel(range.values[0])
            var strD = d.toUTCString();
        });
    }).catch(function (error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
    });
}

Result of getData: