Skip to content

11.3 Number and Date Formats

A number format decides how values look: decimals, thousands separators, percent signs, currency, and how negative numbers and zero are shown. Set it on a metric column, a total or a chart axis with the Numbers group on the HOME tab.

The Numbers Group

Select the values (in design mode), then:

Command What it does
Number format list Pick a format or type your own, e.g. #,##0 or #,##0.0%
Percentage Adds or removes a % sign
Currency Adds your currency symbol, or $ or € from the arrow. $ and € go before the number, other symbols after it.
Thousands separator Adds or removes the thousands separator
Decimal increase / Decimal decrease One decimal more or less
(in the list) Back to the inherited format, e.g. the metric's own format

The corner button of the group opens the Format tab of the format editor, which also has the Empty text setting – what to show in cells without a value, e.g. -.

Writing a Format

Code Meaning Example: 1234.5 shows as
0 A digit, always shown 0.00 → 1234.50
# A digit, shown only when needed #,##0 → 1,235
. Decimal point 0.0 → 1234.5
, Thousands separator #,##0.00 → 1,234.50
E+ Scientific notation 0.00E+00 → 1.23E+03
"text" Text shown as written #,##0 "pcs" → 1,235 pcs
; Separate formats for positive;negative;zero #,##0;(#,##0);- → 1,235, (1,235) for negative, - for zero

Other characters, such as %, $, kr or spaces, are shown as written.

The examples use English separators. Datashop shows and accepts formats with your own separators – with Danish settings, #,##0.00 appears as #.##0,00.

Percentages

The % sign doesn't multiply by 100: a value of 12.5 with format 0.0% shows as 12.5%. Acinta's percentage calculations – Percent of total, A as % of B, index columns – already deliver values ×100, so they fit. A metric that holds 0.125 for 12.5 % needs to be multiplied by 100 in its formula.

When you export to Excel, values with % formats are divided by 100, so Excel shows the same percentage.

What Isn't Supported

Formats work like Delphi number formats, not like Excel:

  • No colors such as [Red] – to show negative numbers in red, use a rule, see 12.3 Conditional Rules.
  • No scaling to thousands or millions with a trailing comma – divide in the metric's formula instead, e.g. Turnover / 1000, and add (thousands) to the title.
  • No *, _, ? or @ codes.

An invalid format is ignored, and the plain value is shown.

Separators and Regional Settings

Decimal and thousands separators come from Windows. To use others, set Decimal separator and Thousand separator in your user settings – leave them empty to keep the Windows settings. See 4.5 Your User Settings.

Dates

Dates and times in tables use the short date format of Windows, or the Short date format in your user settings. Number formats don't apply to dates. Month and day names follow your language.

To show dates as months or years, use a date dimension unit such as Month or Year instead of the date.

Tips – Number and Date Formats

  • Set the number format on the metric itself when it should be the same everywhere – see 10.3 Creating and Editing Metrics. Override it in a visual only when needed.
  • #,##0;(#,##0) shows negative numbers in parentheses, as in financial statements.
  • Use Empty text - to make empty cells visible but quiet.

Common Issues – Number and Date Formats

Problem: A percentage shows as 0.1% instead of 12.5%. - Solution: The value is a fraction (0.125). Multiply by 100 in the metric's formula, or use a quick transformation such as Percent of total, which calculates ×100.

Problem: The format I typed has no effect. - Solution: Check the codes – Excel codes such as [Red] or 0,, don't work, and an invalid format is ignored.