day and month in date string exchange when writting in Excel using Range.values in Office.js library

Question

i am developing an office addin using office.js, https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/range.md#property-access-examples i have to print the date to excel sheet. I am using Range.values to print the dates. Actually i get the date in iso format, after the using javascript function i convert that to only date string in local culture and then assign it to Range.Values property to write it in Excel. on Different culture the behavior is different, for example in Australian culture (Brisbane ) it shows as below

Before being assigned to range.values property my javascript does following

new Date("2016-08-09T14:00:00Z").toLocaleDateString();
new Date("2016-08-13T14:00:00Z").toLocaleDateString();

what is the best approach to fix this. i think if i set cells custom format to text it may work.

If your javascript is really converting the string to local values, Excel should be able to understand it. Make sure the Windows Regional short date settings, and the javascript output, are congruent. If they are not, you will get the results you see. — Ron Rosenfeld
It does appear from your data, that your js date is being output as DMY, but your windows regional short date setting is MDY — Ron Rosenfeld
javascript part is correct, but when it i copy it to excel it automatically switches..excel does that, it doesnt understand that date is correct. — shyam_
The only reason I know of for that to occur is when the settings don't match the string. What, exactly, is the string output from your JS command, for a date after the 12th of the month? What, exactly, is your Windows Regional Short Date Setting (Windows-R ► intl.cpl ) will bring up the Region dialog and display the Short Date setting. — Ron Rosenfeld

Michael Zlatkovsky - Microsoft Michael Zlatkovsky - Microsoft · Accepted Answer · 2016-09-11T17:05:50

There are a couple of ways to do this:

The cleanest and most reliable way is to set the date as an OLE Automation Date value. There is a library called moment.js, and a plugin for it called moment-msdate that can be used to make this very simple.

You can download the libraries locally, but for testing it's probably just easiest to use a CDN that automatically grabs the libraries out of their NPM sources:

<script src="//unpkg.com/moment"></script>
<script src="//unpkg.com/moment-msdate"></script>

The crucial part is that you need to set the .numberFormat before you set the .values. It will likely work either way most of the time, but the failsafe thing to do is always to set the numberFormat before values. Always, for any data type. In this case I'm setting it to the date format I want, and then set the values as a number (Excel's date number format is OK with this)

Excel.run(function(context) {
    var range = context.workbook.getSelectedRange();
    range.numberFormat = [["M-DD-YYYY"]];
    var date = moment("2016-08-09T14:00:00Z");
    console.log(date)
    range.values = [[date.toOADate()]]
    return context.sync();
}).catch(function(error) {
    console.log(error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

If you want to avoid an external library, you can set via strings as well, but you need to make sure that your set number format matches the input that you're giving it. You could also set in one format (YYYY-MM-DD), and then reset the number format to your desired format (M-DD-YYYY or whatever else).

day and month in date string exchange when writting in Excel using Range.values in Office.js library

1 Answers