Show Japanese Imperial Era dates in Excel and why it's wrong

Excel can display dates in many different formats including Japanese Imperial Era.  You don’t need the Japanese version of Excel. Office-Watch.com shows how to display an Era date and how Excel knows which Era to use.

You don’t ‘convert’ a date from western format to Japanese Era.  Like any date in Excel, just change the way a date appears in the cell.  The underlying date value doesn’t change.

Here’s a worksheet with western dd-mmm-yy in Column A.  Cols B & C have the same date value displayed in Japanese Imperial date format – either western letter or Japanese characters.

All the rows have the same date in all three cells, just the cell formatting changes.

Change to Imperial Era

From a cell with an Excel date value, make the change from Format Cells | Custom | Date.

Language (Location): Japanese

Calendar Type: Japanese Emperor Reign

Type: choose western or characters

Upcoming Reign not yet supported

Traditional Japanese dates use an ‘Era’ based on the reign of the Emperor.  The current Heisei era ends on 30 April 2019 when the current Emperor intends to abdicate. 1 May 2019 will be the start of a new era.

The new era name isn’t known yet. It will be announced by the Japanese government on 1 April 2019.

That means dates after 30 April 2019 can be wrong. The July 2018 date is shown as Heisei Era (H31) which isn’t right.  Later, we’ll explain why Excel is doing that.

Microsoft and other software makers are in a bind. They know the Era change is coming but without an Era name they can’t properly update their system.

How Japanese Eras are calculated in Excel

Excel appears to use information in the Windows Registry to show the Japanese Era. This is based on a 2018 Microsoft bug report .

In the Windows Registry, the Japanese Eras are listed at Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\Calendars\Japanese\Eras.  On our test machines in January 2019 it looks like this.

The Name is the start date of the Era (e.g. the Heisei era started on 8 January 1989) while the data is an underscore-separated list of the long and short names of the Era.

The wrong date display above is because there’s no new Era listing to interrupt calculating in Heisei Era.

Possible workaround?

We tried adding a placeholder entry for the new era

2019 05 01   REG_SZ   Data: ??_?_??_?

Or some like

2019 05 01   REG_SZ   Data: UK_U_Unknown_U

But the new era wasn’t recognized in Excel for Windows, even after restarting the computer. We’re not sure why.  You might like to try (if you succeed, please let us know!)

Warning:  If you manually add a new Era to the registry it might not be updated by Microsoft.  After 1 April 2019, the Era name will be announced, and Microsoft will be able to update Windows / Mac. That update might not fix a manual registry addition.