1. Formatting numbers, dates, times and timestamps

There are two ways to describe the formats for the cells in your spreadsheet. Either you can use the tag "NumFormat" in the style options and specify a format string, or you can use one of the internal format numbers defined in Excel. You will find the complete list below. To use those, specify the tag "NumFormaCode" instead.

1. Formatting numbers, dates, times and timestamps
   1. Excel Internal format numbers
      1. Number Formats
      2. Currency Formats
      3. Scientific Formats
      4. Engineering Formats
      5. Date Formats
      6. Time Formats
      7. Timestamp Formats
2. Formatting using format strings
   1. Understanding Number Format Strings
      1. Common Format String Symbols
      2. Date and Time Format Strings
      3. Colour Codes
      4. Conditional Formatting within Number Formats
3. How did we create the internal numbers list?

1.1. Excel Internal format numbers ↑

I haven't found any official reference to the tables below, but I found out by experimenting. Look at the chapter near the bottom for a description of how I found out. For using those numbers below, use them in defining styles for your sheet:

Excel_AddStyle ( wb, "USD", JSON ("NumFormatCode", 7)); 

Wherever you add numbers using the Excel_SetCell function, it will be displayed with a currency symbol (according to regional settings), numbers with two decimals and 1000-group delimiters. The examples below are for Norway.

1.1.1. Number Formats ↑

There are many duplicates in this table, but if you are parsing an Excel sheet, you find the complete table below. To use them, just pick one that fits.

1.1.2. Currency Formats ↑

Numbers with currency symbols according to regional settings. Those below are for Norway.

1.1.3. Scientific Formats ↑

The scientific format uses one digit before the comma and Exponential value.

1.1.4. Engineering Formats ↑

Engineering format uses exponent that are modules of 3.

1.1.5. Date Formats ↑

1.1.6. Time Formats ↑

1.1.7. Timestamp Formats ↑

2. Formatting using format strings ↑

The other way to format cells is to use format strings instead.

Excel_AddStyle ( wb, "USD", JSON ("NumFormat", "USD ### ### ##0.00")); 

This will be a currency format prefixed with USD and a number with two decimals.

2.1. Understanding Number Format Strings ↑

Excel's number format strings are structured to define distinct display formats for different types of data within a single cell. These strings can be divided into up to four sections, separated by semicolons (;), each specifying the format for:

1. Positive Numbers
2. Negative Numbers
3. Zero Values
4. Text Strings

For example, the format string 0.00;[Red]-0.00;0.00;"Text:"@ applies:

• 0.00 to positive numbers
• [Red]-0.00 to negative numbers (displayed in red)
• 0.00 to zero values
• "Text:"@ to text entries, prefixing them with "Text:"

If only one section is provided, it applies to all number types. Two sections mean the first applies to positive numbers and zeros, and the second to negative numbers. Three sections allow separate formatting for positive numbers, negative numbers, and zeros, respectively.

2.1.1. Common Format String Symbols ↑

• 0: Displays insignificant zeros. For instance, 000 applied to the number 5 displays as 005.

• #: Displays significant digits without extra zeros. For example, #.## applied to 5.5 displays as 5.5, and applied to 5 displays as 5.

• . (Period): Acts as a decimal point.

• , (Comma): Serves as a thousands separator. Additionally, placing a comma after a number code scales the number by a thousand. For example, #,##0, applied to 1500 displays as 1.

• %: Multiplies the number by 100 and appends a percent sign.

• ?: Aligns decimal points in numbers with varying lengths by adding spaces.

• * (Asterisk): Repeats the character immediately following it to fill the cell's width. For instance, * (asterisk followed by a space) fills the remaining space with spaces.

• _ (Underscore): Skips the width of the character immediately following it, often used to improve alignment. For example, _( adds space equal to the width of an opening parenthesis.

• @: Placeholder for text in a cell.

• "Text": Displays the enclosed text. For example, 0" units" applied to 5 displays as 5 units.

2.1.2. Date and Time Format Strings ↑

Excel uses specific codes to represent dates and times:

• d: Day number without a leading zero.

• dd: Day number with a leading zero.

• ddd: Abbreviated day name (e.g., Mon, Tue).

• dddd: Full day name (e.g., Monday, Tuesday).

• m: Month number without a leading zero.

• mm: Month number with a leading zero.

• mmm: Abbreviated month name (e.g., Jan, Feb).

• mmmm: Full month name (e.g., January, February).

• yy: Two-digit year.

• yyyy: Four-digit year.

• h: Hour without a leading zero.

• hh: Hour with a leading zero.

• m: Minute without a leading zero.

• mm: Minute with a leading zero.

• s: Second without a leading zero.

• ss: Second with a leading zero.

• AM/PM: Displays time in a 12-hour format with AM or PM.

Note: Excel interprets it as minutes rather than months when using m immediately after h or hh, or immediately before ss.

2.1.3. Colour Codes ↑

You can apply colours to different sections by enclosing the colour name in square brackets:

• [Black]
• [Blue]
• [Cyan]
• [Green]
• [Magenta]
• [Red]
• [White]
• [Yellow]

For example, the format string [Red]0;[Blue]-0;0 displays positive numbers in red, negative numbers in blue, and zeros in the default colour.

2.1.4. Conditional Formatting within Number Formats ↑

Excel allows conditional formatting directly within number formats by specifying conditions in square brackets.

For instance, the format string [>1000]0.00,"K";0.00 displays numbers greater than 1000 as thousands (e.g., 1500 as 1.50

3. How did we create the internal numbers list? ↑

I have not found any official documentation about those numbers, but tons of pages describe how to format numbers in Excel.

After experimenting with some formatting in Excel, looking at the generated stylesheets, and finding the most important ones, I found another better method.

I generated a spreadsheet with the ACF plugin and defined 161 styles in a loop, all with the NumFormatCode from 0 to 160. Then I populated rows using the newly created style to see how it formats numbers, strings, date and time values. Here is the function I made:

function TestBuiltInStyles ()
    string res; 
    string path = desktop_directory ()+ "TestNumFOrmatCodes.xlsx"; 
    print path + "\n"; 
    if ( file_exists (path)) then
        res = delete_file ( path ); 
    end if
    int wb = Excel_Create ( path, "Codes"); 
    excel_addstyle ( wb, "H1", json("Font", "Arial", "FontSize", 18, "Decoration", "Bold")); 
    excel_addstyle ( wb, "H2", json("Font", "Arial", "FontSize", 14, "Decoration", "Bold", 
        "FillColor", "DDDDDD", "Borders", json ("Bottom", linetype_thin))); 
    
    int s = Excel_GetSheetID ( wb, 1); 
    excel_setCell ( s, 1, 1, "Internal NumFormatCodes in Excel", "H1" ); 
    array string hd = {"NumFormatCode", "Value 1.5", "Value 2", "Text", "Date", "Time", "TimeStamp" }; 
    excel_setColumns ( s, 2, 1, hd, "H2"); 
    long todayInt = date(now())-date("1900-01-01", "%Y-%m-%d")+2;
    float tsValue = 0.5 + todayInt; 
    int i, row; 
    string style; 
    for (i=0, 160)
        style = "Code"+i; 
        excel_addstyle ( wb, style, json ("NumFormatCode", i));
        row = i+3; 
        excel_setcell( s, row, 1, i);
        excel_setcell( s, row, 2, 1.5, style);
        excel_setcell( s, row, 3, 2, style);
        excel_setcell( s, row, 4, "Some Text", style);
        excel_setcell( s, row, 5, todayInt, style);
        excel_setcell( s, row, 6, 0.5, style);
        excel_setcell( s, row, 7, tsValue, style);
    end for
    excel_close ( wb); 
    return "OK"; 
end

Then I got a spreadsheet like this:

Then I just had to categorize it for this documentation.

Good luck with your cell formatting.