Wareki Dates and ▲ Negatives — Excel Number Format Codes in a JavaScript Spreadsheet

A date is not a string, and in Japan it is not even one calendar. A form headed for a ministry, a bank, or a tax office is expected to carry the 和暦 — the era year — and the first year of an era is written 元年, never 1年. Get that wrong and the document comes back.

The interesting part is that this is not a rendering problem you solve once in your view layer. In a spreadsheet the value is a number, the display is a format code, and the code travels with the cell — through the grid, into print, into the PDF, and back out of the .xlsx the accounting team sent you. This article is about that layer: what Excel’s number format codes can express, and how far ReoGrid Web takes them.

Number formatting is available in Lite, the free tier. Nothing in this article needs a license key.

Why not just Intl.DateTimeFormat?

You can get an era string out of the platform, and for a label in your UI that is often the right answer:

const d = new Date(2019, 6, 1); // 2019-07-01, the first day of 令和

new Intl.DateTimeFormat('ja-JP-u-ca-japanese', { dateStyle: 'long' }).format(d);
// → '令和元年7月1日'   ✅

new Intl.DateTimeFormat('ja-JP-u-ca-japanese', {
  era: 'long', year: 'numeric', month: 'numeric', day: 'numeric',
}).format(d);
// → '令和1/7/1'        ⚠️ the 元 is gone, and so is the 年月日

That second result is the shape of the problem. Intl decides the punctuation, the separator, and — depending on which options you pass — whether you get 元 at all. You are negotiating with a formatter, not describing a layout.

A number format code is the opposite: you write exactly the output you want, one string, and it belongs to the cell rather than to the code path that happened to render it.

worksheet.cell('B2').setFormat('[$-ja-JP-x-gannen]ggge"年"m"月"d"日"');
// 2019-07-01 → 令和元年7月1日
// 2024-12-10 → 令和6年12月10日

Same cell, same format, every date. That string is also what Excel itself stores, so a form authored by someone in accounting arrives with its formatting intact.

The model: one value, many displays

A cell holds a value. A format code decides how that value is drawn. The two never merge — sort a column of dates and it sorts by the underlying serial number, not by the text you see.

import { createReogrid, NumberFormat } from '@reogrid/lite';

const grid = createReogrid({ workspace: '#grid' });
const ws = grid.worksheet;

// One cell
ws.cell('B2').setValue('4999').setFormat('¥#,##0');

// A whole range
ws.range('B2:B20').setFormat(NumberFormat.currency('¥', 0));

// Property assignment reads and writes the same thing
ws.cell('C3').format = '0.00%';
ws.cell('C3').format;        // → '0.00%'
ws.cell('C3').format = undefined;   // clear it

Row/column indices work too, which is what you want inside a loop:

ws.setCellInput(row, col, '45636');
ws.setCellNumberFormat(row, col, 'ggge"年"m"月"d"日"');

ws.getCellNumberFormat(row, col);    // → the code
ws.clearCellNumberFormat(row, col);  // → back to General
ws.getDisplayText(row, col);         // → '令和6年12月10日'

getDisplayText is the same call the renderer, the print path and the PDF exporter use, so whatever it returns is what lands on paper.

Getting a date into the cell in the first place

Excel stores dates as serial numbers — day 1 is 1900-01-01 — and ReoGrid Web does the same. There are two ways in.

Type it, or set it as text. Input that looks like a date is recognized and converted:

ws.setCellInput(2, 1, '2024/12/10');
// stored as 45636, format set to 'yyyy/m/d'

The patterns recognized are yyyy/m/d, yyyy-m-d, m/d/yyyy, and those first two with a trailing h:mm or h:mm:ss.

Or set the serial directly, which is what you do when the number came from an import or a date library:

ws.setCellInput(2, 1, '45636');
ws.cell(2, 1).setFormat('ggge"年"m"月"d"日"');

The ordering gotcha

Auto-detection is deliberately skipped when the cell already has a number format — otherwise applying a format would silently rewrite user input. That makes the order significant:

// ✅ value first, then format
ws.setCellInput(2, 1, '2024/12/10');            // → 45636 (a real date)
ws.cell(2, 1).setFormat('ggge"年"m"月"d"日"');  // → 令和6年12月10日

// ❌ format first
ws.cell(3, 1).setFormat('ggge"年"m"月"d"日"');
ws.setCellInput(3, 1, '2024/12/10');            // stays the string '2024/12/10'

In the second case the cell never becomes a number, so there is nothing for a date format to format. When you are populating a pre-formatted template, write serials rather than date-shaped strings and the ambiguity disappears.

Era tokens

Two families of tokens, and they combine with the ordinary date tokens you already know:

Put together:

ws.cell('B2').setFormat('ggge"年"m"月"d"日"');       // 令和6年12月10日
ws.cell('B3').setFormat('ge/m/d');                     // R6/12/10
ws.cell('B4').setFormat('gge.m.d');                    // 令6.12.10
ws.cell('B5').setFormat('gggee"年"mm"月"dd"日"');    // 令和06年12月10日
ws.cell('B6').setFormat('ggge"年度"');                // 令和6年度

Quoted text is literal, so "年" prints 年 rather than being read as a token. That matters — an unquoted d inside your label would come out as a day number.

Era boundaries are dates, not years

The era switch happens on the day the era began, so a date in early January 1989 is 昭和64年 and a date a week later is 平成元年:

Dates before 明治 have no era to name: the g and e tokens produce nothing, and only the literal parts of your format survive. If your data reaches back that far, pick the section by condition or fall back to a Gregorian format.

元年 — the whole reason this article exists

An era’s first year is written 元年. Excel expresses that with a locale tag, and ReoGrid Web reads the same one:

ws.cell('B2').setFormat('[$-ja-JP-x-gannen]ggge"年"m"月"d"日"');
// 2019-07-01 → 令和元年7月1日
// 2020-07-01 → 令和2年7月1日     (only year 1 changes)

The older numeric spelling that legacy files carry is understood as well:

ws.cell('B3').setFormat('[$-x-gannen411]ggge"年"m"月"d"日"');

Without the tag you get 令和1年 — which is what Excel does too, and what a ministry form will bounce.

Two details worth knowing. The tag applies at both e and ee widths, because 元 is not a number and cannot be zero-padded. And it is scoped to the section it appears in, so in a multi-section format you put it on the section that draws the date.

Sections: positive, negative, zero

A format code is up to four ;-separated sections. The first three are chosen by sign:

positive ; negative ; zero

ws.range('B2:E20').setFormat('#,##0;[赤]"▲"#,##0;"−"');
//  1,250,000 → 1,250,000
// -1,250,000 → ▲1,250,000   (in red)
//          0 → −

The critical behavior: the negative section receives the absolute value. That is exactly why the Japanese accounting convention works — you write "▲"#,##0 and get ▲1,250,000, not ▲-1,250,000. A P&L column with this one format code reads the way a Japanese financial statement is supposed to read, with no per-cell logic on your side.

Percent changes take the same treatment:

ws.cell('F5').setFormat('0.0%;[赤]"▲"0.0%');
// 0.443 → 44.3%      -0.088 → ▲8.8% (red)

Bracket colors

A color in square brackets recolors the cell text for the section it sits in. Both the English and the Japanese names are accepted:

Excel’s indexed palette works too — [Color 3] or [色3] for indices 1–16.

The resolved color is readable, which is handy when you are mirroring a cell’s appearance somewhere else in your UI:

ws.setCellNumberFormat(5, 2, '#,##0;[赤]"▲"#,##0');
ws.setCellInput(5, 2, '-8000');
ws.getCellFormatColor(5, 2);   // → '#FF0000'

A format color takes precedence over the cell’s own style.color, both on screen and in PDF export (on v1.5 the PDF used the style color; from v1.6 it follows the screen). Browser print takes a different route — it renders through HTML, which still uses the cell style’s color — so a report printed from the browser loses its red negatives. Set the style color as well when that matters, or drive the color from conditional formatting, which writes a real style that every path honors.

Conditional sections

Instead of letting the sign pick the section, you can put a comparison in front of it. Up to two conditions, plus a fall-through:

ws.range('C2:C50').setFormat('[>=100000]"要審査";[>=10000]"確認";"通常"');

The first matching section wins; if none match, the first section without a condition is used. Unlike the sign-based path, a conditional section gets the value as-is — no implicit absolute value — so a - will show if the value is negative and your section draws one.

Pair it with trailing-comma scaling, where each trailing comma divides the displayed number by 1,000:

ws.range('D2:D50').setFormat('[>=1000000]#,##0,,"百万円";[>=1000]#,##0,"千円";0"円"');
// 24,500,000 → 25百万円
//  1,250,000 → 1百万円
//      8,500 → 9千円
//        320 → 320円

A sheet starts at 40 rows, and a range that reaches past the last row is dropped without a warning — formats, styles, conditional formats and validation rules all fall silently on the floor. Call setGridSize() (or size the sheet from your data) before addressing something like B2:B100.

One thing to keep in mind: the scaled number is rounded to the digits the pattern asks for, so #,##0, on 8,500 reads 9千円 — reach for it in summary columns, not where the exact figure matters. Add decimals (#,##0.0, → 8.5千円) when you need the precision back; on v1.5 keep the scaled pattern an integer one, as a fraction combined with trailing commas is only scaled from v1.6 onward.

The value in the cell never changes — SUM still adds the real numbers.

Currency, and the locale bracket

Three ways to attach a symbol, all equivalent in output:

ws.cell('B2').setFormat('¥#,##0');                    // ¥1,250,000
ws.cell('B3').setFormat('#,##0"円"');                 // 1,250,000円
ws.cell('B4').setFormat('[$¥-411]#,##0');             // ¥1,250,000

The third is the form Excel writes when the currency is locale-tagged: [$symbol-locale] contributes the symbol and drops the locale id. Files that came from a Japanese Excel are full of these, and they import cleanly.

The NumberFormat helpers generate the common cases so you are not hand-writing strings everywhere:

NumberFormat.number(2);                    // '#,##0.00'
NumberFormat.percent(1);                   // '0.0%'
NumberFormat.currency('¥', 0);             // '¥#,##0'
NumberFormat.currency('円', 0, 'suffix');  // '#,##0円'
NumberFormat.date('yyyy-mm-dd');           // 'yyyy-mm-dd'
NumberFormat.time('h:mm AM/PM');           // 'h:mm AM/PM'

A worked fragment: a request form header

Put the pieces together and a 官公庁-style form header is a handful of lines:

import { createReogrid, NumberFormat } from '@reogrid/lite';

const ws = createReogrid({ workspace: '#grid' }).worksheet;

const WAREKI = '[$-ja-JP-x-gannen]ggge"年"m"月"d"日"';
const YEN    = NumberFormat.currency('¥', 0);
const ACCT   = '#,##0;[赤]"▲"#,##0;"−"';

// 申請日 / 決裁日
ws.cell('B2').setValue('申請日').setStyle({ bold: true });
ws.setCellInput(1, 2, '2019/7/1');
ws.cell(1, 2).setFormat(WAREKI);          // 令和元年7月1日

ws.cell('B3').setValue('決裁日').setStyle({ bold: true });
ws.setCellInput(2, 2, '2024/12/10');
ws.cell(2, 2).setFormat(WAREKI);          // 令和6年12月10日

// 金額欄
ws.cell('B5').setValue('請求金額').setStyle({ bold: true });
ws.setCellInput(4, 2, '1250000');
ws.cell(4, 2).setFormat(YEN);             // ¥1,250,000

ws.cell('B6').setValue('前年差額').setStyle({ bold: true });
ws.setCellInput(5, 2, '-84000');
ws.cell(5, 2).setFormat(ACCT);            // ▲84,000 (red)

// 期区分 — one code, no branching
ws.cell('B7').setValue('規模区分').setStyle({ bold: true });
ws.setCellInput(6, 2, '1250000');
ws.cell(6, 2).setFormat('[>=1000000]#,##0,,"百万円";[>=1000]#,##0,"千円";0"円"'); // 1百万円

Every one of those cells still holds a number. Sort them, sum them, feed them to a formula — the format is presentation, and only presentation.

What travels, and what doesn’t

A few Excel niceties are parsed but produce no output, so a code copied from Excel won’t break — it just won’t pad: _c (width placeholder) and *c (fill character) are consumed silently. The @ text section and [DBNum1]-style kanji numerals are not supported.

Wrapping up

Japanese business documents have rules that are older than any of our frameworks, and they are not negotiable: 元年 rather than 1年, ▲ rather than a minus sign, 円 in the place the form puts it. Excel encoded those rules in format codes decades ago, and the fastest way to satisfy them in a browser is to speak the same language — one string per cell, attached to the value, honored by the grid and the printer alike.

Open the Japanese Era & Color Formats demo to see every code in this article rendering live, then the number formatting docs for the full token reference. If the form you are reproducing is currently an .xlsx, start with moving Excel layout forms to the web — the formats come across with it — and generating PDF invoices in the browser when it has to end up on paper.