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.
