Number format hh:mm is given as h:mm on Excel for Mac

[Rinzwind]

On Excel for Mac, for a cell with number format hh:mm, Range.numberFormat gives the format as h:mm.

The Script Lab snippet given below sets up an example as follows:

const range = sheet.getRange("A1:B1");
(range.values as any) = 9 / 24;
range.numberFormat = [["h:mm", "hh:mm"]];

On Excel for Mac (v16.83, 24031120; using macOS 13.6.6), the snippet then logs:

range.text: 9:00,09:00
range.numberFormat: h:mm,h:mm
range.numberFormatLocal: u:mm,uu:mm

Note that the text for both cells is different (9:00 versus 09:00), and that numberFormatLocal also gives different formats for the cells as expected (u:mm versus uu:mm). However: numberFormat unexpectedly gives the same format h:mm for both cells.

I previously reported this as issue #2107 which was closed due to inactivity despite having been confirmed to be reproducible (bug 5411990) and the bug still being present in the latest version of Excel (version number given above).

Complete Script Lab snippet:

name: 'Test Number Format HH:MM'
description: ''
host: EXCEL
api_set: {}
script:
  content: |
    (async () => {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const range = sheet.getRange("A1:B1");
        (range.values as any) = 9 / 24;
        range.numberFormat = [["h:mm", "hh:mm"]];
        await context.sync();
      });
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const range = sheet.getRange("A1:B1");
        range.load(["text", "numberFormat", "numberFormatLocal"]);
        await context.sync();
        console.log(`range.text: ${range.text}`);
        console.log(`range.numberFormat: ${range.numberFormat}`);
        console.log(`range.numberFormatLocal: ${range.numberFormatLocal}`);
      });
    })();
  language: typescript
template:
  content: ''
  language: html
style:
  content: ''
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

[microsoft-github-policy-service[bot]]

Thank you for letting us know about this issue. We will take a look shortly. Thanks.

[Rinzwind]

I reported this as an issue with Excel on Mac, but it actually also applies to Excel on Windows (version 2408 Build 16.0.17928.20114) and is related to the ‘Regional format’ setting. When ‘Regional format’ is set to ‘Dutch (Belgium)’ (see screenshots below), the snippet I gave logs the following, with numberFormat giving a different format for the two cells as expected:

range.text: 9:00,09:00
range.numberFormat: h:mm,hh:mm
range.numberFormatLocal: u:mm,uu:mm

When ‘Regional format’ is set to ‘Dutch (Netherlands)’ (see screenshots below), the snippet logs the following, with numberFormat unexpectedly giving the same format for the two cells:

range.text: 9:00,09:00
range.numberFormat: h:mm,h:mm
range.numberFormatLocal: u:mm,uu:mm

The following screenshots show the ‘Regional format’ setting, note the difference in the number of digits for the hour for the short time format:

[shanshanzheng-dev]

Hi @Rinzwind Thanks for the detail. It has been put on our backlog <bug #5411990>. We'll take a look and unfortunately have no timelines to share at this point. Thanks.