Datetimeformatinfo reads locale correctly but excel interprets date incorrectly

[Jeroendevr]

Provide required information needed to triage your issue

When pasting dates from Office Scripts into Excel which has locale nl-NL it assumes the dates are in mm-dd-yyyy format and therefore dates are being converted incorrectly.

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: Mac
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: 16.78
• Operating System: MacOS Sonoma

Expected behavior

Using the DatetimeFormat Documentation Example

/**
 * This script sets the value of a cell to a date string for January 2, 2023.
 * It writes the day or month first in the string based on system settings.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first cell in the current worksheet.
  const cell = workbook.getActiveWorksheet().getCell(0,0);

  // Get the date format.
  const cultureInfo : ExcelScript.CultureInfo = workbook.getApplication().getCultureInfo();
  const systemDateTimeFormat : ExcelScript.DatetimeFormatInfo = cultureInfo.getDatetimeFormat();
  const shortDatePattern : string = systemDateTimeFormat.getShortDatePattern();

  // Determine if the date should start with the month or day.
  if (shortDatePattern.startsWith("m")) {
    cell.setValue("1/2/2023");
  } else {
    cell.setValue("2/1/2023");
  }
}

Current behavior

The value of the cell becomes 01-02-2023 which is first of february.

This is also the case If I replace the / with -

/**
 * This script sets the value of a cell to a date string for January 2, 2023.
 * It writes the day or month first in the string based on system settings.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the first cell in the current worksheet.
    const cell = workbook.getActiveWorksheet().getCell(0, 0);

    // Get the date format.
    const cultureInfo: ExcelScript.CultureInfo = workbook.getApplication().getCultureInfo();
    const systemDateTimeFormat: ExcelScript.DatetimeFormatInfo = cultureInfo.getDatetimeFormat();
    const shortDatePattern: string = systemDateTimeFormat.getShortDatePattern();
    console.log(shortDatePattern)

    // Determine if the date should start with the month or day.
    if (shortDatePattern.startsWith("m")) {
        cell.setValue("1-2-2023");
        console.log("Starst with m")
    } else {
        cell.setValue("2-1-2023");
        console.log("starts with d")
    }
}

Result

dd-mm-jjjj
starts with d

So the correct evaluation took place and the correct date was pasted, however Excel intepreted it incorrectly.

[donlvMSFT]

Hi @Jeroendevr ,

Thanks for reporting this, I've tried to repro this issue, but seems not same behavior with yours.

I think this should be related about the difference Excel culture setting, could you share how you've set the culture before running the script? That would help us a lot to locate the cause.

Thanks.

[Jeroendevr]

Hi @donlvMSFT, yes the culture settings are different indeed. This is what I meant with locale but that was not entirely clear perhaps.

Culture settings

The macOS system is set to

• language Nederlands (a.k.a. Dutch)
• region Nederland (a.k.a. The Netherlands)

As I understand correctly from the Excel Help file, Microsoft Office automatically uses the primary language of the system. It does so correctly because console.log(shortDatePattern) yields

• dd-mm-jjjj in my case
• m/d/yyyy in your reproduction (probably set to us or similar)

P.S. btw only changing the interface language of Excel keeps the console.log(shortDatePattern) to dd-mm-jjjj which I find correct as that is the system settings. So when you try to reproduce setting the Excel interface to Dutch is not sufficient it has to be the system language.

[Jeroendevr]

Windows 11

Also on my VM I have the same results as on macOS, settings are:

• language English (United States)
• Country Netherlands
• Regional format Dutch (Netherlands)

[donlvMSFT]

Got you @Jeroendevr , I did some validation under your setting and found this is a tricky scenario.

1. The default culture setting of the workbook is Dutch, which is correct.
2. The Office Script execution will put the raw string into the cell (follow US culture) and then after insertion, formatted follow the workbook culture.

So in you sample code,

    // Determine if the date should start with the month or day.
    if (shortDatePattern.startsWith("m")) {
        cell.setValue("1-2-2023");
        console.log("Starst with m")
    } else {
        cell.setValue("2-1-2023");
        console.log("starts with d")
    }

if and else actually use different dates, if uses Jan-2, else actually uses Feb-1, which shows as 1-2-2023 as format d-m-jjjj.

[Jeroendevr]

Thanks for explaining the behaviour. So you are saying that all dates set to cells should follow US culture. When set automatically formatting according to the default culture is applied? Can you give me a reference in the documentation where this behaviour is defined?

This is not what I read in DatetimeFormat documentation. There it states that the value should be set according to the culture settings, or is this documentation incorrect?

[Jeroendevr]

A small update which maybe helps

Date Object

Creating a Date object en converting pasting setting the string value to a cell also assumes US culture

function main(workbook: ExcelScript.Workbook) {
    // 2023-01-02 on Cell A1
    let dateRange = workbook.getActiveWorksheet().getRange("A1")
    let date = new Date(2023, 0, 2)
    dateRange.setValue(date.toLocaleDateString())
}

This means not only string values, but also values derived from the Date object cannot be converted to Locale formats.

[Jeroendevr]

Hi @donlvMSFT, just a heads-up. Can you confirm it is correct or incorrect behaviour?