ms office - Disable the automatic conversion to Date -

in 1 workbook (from else) need analyse, use formula construct list of strings: 04-09-01, 04-09-02, 04-09-03, etc, have general format. in part of code, need copy these values somewhere else. however, because these values quite special, automatically considered date (whereas not dates users) , transformed 09/04/2001, 09/04/2002, 09/04/2003, etc. consequence, values changed, , calculation based on these pasted values lead errors.

here test code:

function test () {     excel.run(function (ctx) {         var r0 = ctx.workbook.worksheets.getitem("sheet1").getrange("a2:a10");         var r1 = ctx.workbook.worksheets.getitem("sheet1").getrange("f2:f10");         r0.load(["values"]);         return ctx.sync()             .then(function () { console.log(r0.values.tostring()); r1.values = r0.values; })             .then(ctx.sync)             .then(function () { r1.load(["values"]); })             .then(ctx.sync)             .then(function () { console.log(r1.values.tostring()); })     }); } 

the result in console shows values changed:

and in excel, shows:

note that, excel not systematically transform these values dates. instance, if value-only copy 04-09-01 cell. excel raise warning , suggests convert date, users ignore warning , keep 04-09-01 is:

so question is, there way or workaround disable automatic conversion in javascript api, faithfully copy values?

edit 1:

i tried use numberformat keep initial formats of range. first, put a2:a0 follows general format.

then, run following code:

function test () {     excel.run(function (ctx) {         var r0 = ctx.workbook.worksheets.getitem("sheet1").getrange("a2:a10");         var savevalues, savenumberformat;         r0.load(["values", "numberformat"]);         return ctx.sync().then(function () {             savenumberformat = r0.numberformat;             savevalues = r0.values;             r0.numberformat = savenumberformat;             r0.values = savevalues;         });     }); } 

the result turned out follows, , has date format.

so restoring of numberformat not help?

edit 2: made example copies range another. want r1 have same number format , values r0. result shows 04-09-01 general in r0 produces 04/09/2001 date in r1. basically, problem same in previous example: numberformat cannot faithfully copied or restored.

function test () {     excel.run(function (ctx) {         var r0 = ctx.workbook.worksheets.getitem("sheet1").getrange("a2:a10");         var r1 = ctx.workbook.worksheets.getitem("sheet1").getrange("k2:k10");         r0.load(["values", "numberformat"]);         return ctx.sync()             .then(function () { r1.numberformat = r0.numberformat; })             .then(ctx.sync)             .then(function () { r1.values = r0.values; })         }); } 

you can copy number format after, maybe like:

function test () {     excel.run(function (ctx) {         var ws = ctx.workbook.worksheets.getitem("sheet1");         var r0 = ws.getrange("a2:a10");         var r1 = ws.getrange("k2:k10");          r0.load(["values", "numberformat"]);         return ctx.sync().then(function () {              r1.numberformat = "@";              r1.values = r0.values;              r1.numberformat = r0.numberformat;          })     }); }