apply_numfmt: Format Values using OOXML (Spreadsheet) Number Format Codes

In openxlsx2: Read, Write and Edit 'xlsx' Files

Format Values using OOXML (Spreadsheet) Number Format Codes

Description

This function emulates a spreadsheet formatting engine. It takes numeric, date, or character values and applies an OOXML format code to produce a formatted string. It supports standard number formatting, date/time, elapsed durations, fractions, and conditional formatting sections.

Usage

apply_numfmt(value, format_code)

Arguments

value	A vector of values to format. Supports numeric, Date, POSIXct, character (ISO dates or times), hms, or difftime.
format_code	A character vector of format strings (e.g., "#,##0.00", "yyyy-mm-dd", or "[h]:mm:ss").

Details

The function splits the format_code into up to four sections separated by semicolons (Positive; Negative; Zero; Text).

• Date and Time: Supports standard tokens (yyyy, mm, dd, hh, ss) and AM/PM toggles.

• Durations: Supports elapsed time tokens in square brackets, such as ⁠[h]⁠, ⁠[m]⁠, and ⁠[s]⁠, calculating total units.

• HMS Handling: Character strings in "HH:MM:SS" format are automatically coerced to a date-time object using a base date to allow clock and duration formatting.

• Numbers: Supports thousands separators (⁠,⁠), precision, percentage conversion, and scientific notation (⁠E+⁠).

• Fractions: Uses the Farey algorithm to approximate decimals as fractions (e.g., ⁠?/?⁠, ⁠# ?/?⁠).

• Conditionals: Evaluates bracketed conditions like ⁠[<1000]⁠ to select the appropriate formatting section.

• Literals: Handles escaped characters (⁠\\⁠), quoted text ("text"), and the text placeholder (@).

Rounding is based on the R function round() and does not match spreadsheet software behavior.

Value

A character vector of formatted strings.

Examples

# Numeric formatting
apply_numfmt(1234.5678, "#,##0.00") # "1,234.57"

# Date and Time
apply_numfmt("2025-01-05", "dddd, mmm dd") # "Sunday, Jan 05"
apply_numfmt("13:45:30", "hh:mm AM/PM")    # "01:45 PM"

# Durations
apply_numfmt("1900-01-12 08:17:47", "[h]:mm:ss") # "296:17:47"

# Fractions
apply_numfmt(1.75, "# ?/?") # "1 3/4"

openxlsx2 documentation built on Jan. 16, 2026, 5:06 p.m.