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.

score 6 · Accepted Answer · edited May 08 '18 at 21:49

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:

hi, i am setting the value of range using the date in string, i am not able to set the value type as date. so i dont get the numeric value, when i read the cell using javascript. i just get the date in string. i dont see this number you are saying. i am using api from https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/range.md — shyam_, Jun 14 '16 at 17:51
What’s the date format in your code? We need to specify the correct format Excel could recognize. See the update for the code for testing. — Fei Xue, Jun 15 '16 at 09:07
Also note that there is a .NumberFormat property which allows you to set something like "yyyy-mm-dd". See https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4 for the full list of options — Michael Zlatkovsky - Microsoft, Jun 15 '16 at 15:20
hi, there is something wrong about the solution, it retun the time in UTC which is equivalent to local time. if you actually convert that to local time it will have different result. can you please correct this. — shyam_, Jul 03 '16 at 20:40
i modified the function like this function getJsDateFromExcel(excelDateValue) { var date = new Date((excelDateValue - (25567 + 2)) * 86400 * 1000); var localTime = new Date(date.getTime() + (new Date()).getTimezoneOffset() * 60000); return localTime; } still it give wrong value with respect to second — shyam_, Jul 03 '16 at 21:13
This is because that the Excel doesn't handle the timezone. If you want to deal with the time zone, you need to convert the date time yourself. For example, you can convert the local time to UTC and store the UTC date time Excel. — Fei Xue, Jul 04 '16 at 01:42

how to handle date type in excel addin office.js

1 Answers1