Disable the automatic conversion to Date

Question

In one workbook (from somebody else) that I need to analyse, they use a formula to construct a list of strings: 04-09-01, 04-09-02, 04-09-03, etc, which have general as format. In a part of my code, I will need to copy these values to somewhere else. However, because these values are quite special, they are automatically considered as Date (whereas they are clearly not dates for users) and transformed to 09/04/2001, 09/04/2002, 09/04/2003, etc. As a consequence, the values are completely changed, and the calculation based on these pasted values lead to errors.

Here is a 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 the console shows the values are completely changed:

And in Excel, it shows:

Note that, Excel itself does NOT systematically transform these values to dates. For instance, if we value-only copy 04-09-01 to another cell. Excel does raise a warning and suggests to convert it to a date, but users could well ignore this warning and keep 04-09-01 as it is:

So my question is, is there a way or workaround to disable this automatic conversion in JavaScript API, so that we could faithfully copy values?

Edit 1:

I tried to use numberFormat to keep the initial formats of a range. First, I put A2:A0 as follows with General as format.

Then, I run the 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 to be the follows, and has Date as format.

So the restoring of numberFormat does not help?

Edit 2: I made an example which copies a range to another. I want r1 to have exactly same number format and values as r0. But the result shows that 04-09-01 as general in r0 produces 04/09/2001 as Date in r1. So basically, the problem is the same as in the previous example: numberFormat cannot be 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; })
        });
}

Kaenel

You can also try the CoercionType.Text shown here msdn.microsoft.com/en-us/magazine/jj891051.aspx – Gabon

Answer 1

You can copy the number format after, so maybe something 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; 
        })
    });
}

Gabon

OK, that works... it seems that we could also simplify that to .then(function () { r1.numberFormat = "@"; r1.values = r0.values; r1.numberFormat = r0.numberFormat; }). So the basic idea is set the number format of the range to text ("@") BEFORE you apply the values, the values should stay as is, and then we apply the real numberFormat... – Kaenel

It seems that you modified your code. You need to insert in the middle r0.load(["values", "numberFormat"]); and return ctx.sync().then( etc... – Kaenel

ok .. the examples that I saw online seemed like it might work without them, and I was testing it only in the desktop version of Excel that doesn't use anything like that. – Gabon

Answer 2

You should be able to do one of two things:

1) If you set the number format of the range to text ("@") BEFORE you apply the values, the values should stay as is.

2) You can also prepend an apostrophe (') to the beginning of the text, to force Excel to treat it as plain text regardless of what the internal engine would usually treat it as.

UPDATE

There's actually two things at stake here. There is the displayed text vs. the underlying value.

To get the text, access the range.text property (2D array). This will be the text as displayed in the UI. To get the underlying value, access the range.values property (also a 2D array)

If you are copying values from one place to another, or storing them and then restoring, be sure to store both the range.numberFormat and range.values. And then restore them back, with .numberFormat first.

UPDATE 2

See the screenshot below. The code works as expected.

~ Michael Zlatkovsky, Developer on Office Extensibility Platform, MSFT

Pyrology

How could I set the number format of the range to text ("@") by JavaScript API? – Kaenel

The problem is, this is a specific example, but in general, I don't know where these ambiguous values are and need to apply (by JavaScript API) a general solution to all the values I want to copy... – Kaenel

Are you needing to copy values from one range to another, or just read back values? – Pyrology

Actually, what I need is to save the values in variables, and do some operations over the cells (e.g., delete contents, modify contents), and finally restore the initial values. – Kaenel

You're setting the number format to itself, so it's a no-op. I don't see you copying the values. – Pyrology

@Kaenel change r0.numberFormat = saveNumberFormat; to r0.numberFormat = "@"; before r0.values = saveValues; – Gabon

@Slai, by r0.numberFormat = "@", it changes the number format to text, however, I want to restore the initial numberFormat (in this example, it is general). It is just not always correct to apply text to everything. – Kaenel

@SoftTimur, I think there's a disconnect here. If you want to apply your data for Range1 to Range2, set Range2 to have its numberFormat and values property to be equal to Range1's (once you've loaded them). If you only want to look at the current display values, load "text" – Pyrology

@MichaelZlatkovsky I have added another "copy" example. The problem is that I don't want to always hard-apply a fixed numberFormat for the target range; I want to copy exactly the same numberFormat as the source range. – Kaenel

@SoftTimur, honestly, I see to fail what's wrong with the code you have in Edit 2. That is precisely the code I would have written, except for the ".then(ctx.sync)" in the middle which you don't need, where you can just combine both calls into a single function. Part of copying values is copying its number formatting. Sure, it's two calls instead of one, but other than that I really don't see a problem (and the fact that they're two separate properties is by design) – Pyrology

So can you reproduce my problem with the code of Edit 2? So, is it a bug? – Kaenel

See the image in my Update 2. The code (which is effectively your "Edit 2" code, but slightly optimized) works exactly as expected. – Pyrology

Your example is different from the mine. I use your code to copy 04-09-01 as General in r0 still produces 04/09/2001 as Date in r1. Maybe the bug only happens when copying from a General number format? – Kaenel

@SoftTimur, that might be it, yes, the General format. But how do you actually get into a position where it's a date AND with a General number format? For me, if I enter in a date and then set to general, I get a number. And typing in the date again as "July 4" will once again set it to a date format... – Pyrology

I explained that in the OP. Actually, I didn't produce this example, it is a workbook from somebody else. I discovered that because my program raised errors on this workbook. He didn't want to put Date, for him 04-09-01, 04-09-02 etc. are just some string codes. But Excel may consider them as Date, that's why Excel proposes (but not forces) some conversion when copying values. Whereas, JavaScript API forces this conversion to Date. – Kaenel

@SoftTimur, do you mind checking what VBA would have done in this case. And again, if you can find out how that "somebody else" got into a general (as opposed to "Text") format while having a date, I'd love to hear it. – Pyrology

In the first image of the OP, the formula bar shows the user used some functions to make these codes, and as a result, their numberFormat is General, rather than Date or Text. From the headers in Row 1, clearly the user doesn't consider these codes as dates. I have tested VBA, the behavior is same as JavaScript API: copying these ambiguous values transforms automatically their format from General to Date. – Kaenel