Japanese Gannen and Ichinen date support in Excel, Office and Windows
As part of Microsoft’s support for the new Japanese Imperial era, they’ve added support for traditional Gannen writing of dates as well as the newer Ichinen style.
Windows 10 now supports Gannen style Japanese dates but that support doesn’t flow through to Excel formatting. Normally the default date presentation in Excel uses the Windows defaults, but not in this case.
Gannen date support in Excel is possible but very clumsy (to put it kindly).
Gannen (元年) vs. Ichinen (1年)
We’ll let Microsoft explain the difference between Gannen and Ichinen dating in the first year of a new Imperial era.
“In historical practice, for the first year of the era, a special character, “Gan (元),” whose Kanji character means “origin” or “beginning,” is used in place of the number “Ichi (1).”
The first year “Gannen (元年)” continues until the end date of the Gregorian calendar year, December 31.”
Here’s now the two date styles appear in Excel.
Up to now Windows has only used the Ichinen system. Now either Gannen or Ichinen are options.
Ichinen dates in Excel
It’s easy to use Ichinen style dates in Excel, as we’ve shown before.
Format Cells (Ctrl + 1) | Number | Category | Date …
Locale: Japanese
Calendar Type: Japanese Emperor Reign.
Assuming your Windows and Office has been updated with the new Reiwa era details, dates will show up correctly.
Gannen style dates in Excel
It’s not so easy for Gannen date style in Excel. Regular Excel users might expect it to follow the Japanese date format from Windows, which is the usual practice. Unfortunately, Microsoft’s normal practice doesn’t apply to Gannen styling of Japanese dates.
According to Microsoft, Excel will use Ichinen date formatting regardless of the Windows Gannen date setting (see below).
The workaround, supplied by Microsoft is to apply a custom date format like this:
[<=43585][$-ja-JP]ggge"年"m"月"d"日";[>=43831]ggge"年"m"月"d"日";ggg"元年"m"月"d"日"
Obviously, don’t try to retype that string, copy/paste it into a Format Cells (Ctrl + 1) | Number | Category | Custom like this:
If you need it regularly, make a new cell style that includes the formatting string.
How does it work?
There are three formatting choices, separated by a semi=colon. The first two formats have date conditions using Excel serial date numbers:
[<=43585] 43585 is 30 April 2019 – the last day of the previous Imperial Era. This condition is for all dates after (greater than or equal to) 30 April 2019.
If true the formatting is: [$-ja-JP]ggge”年“m”月“d”日“
[>=43831] 43831 is 1 January 2020 – the last day that Gannen style dating applies.
If true, the formatting is: ggge”年“m”月“d”日”
The formatting after the last semi-colon applies if neither of the two conditions apply (i.e not between 30 April 2019 and the end of 2019).
Else use this formatting: ggg”元年“m”月“d”日“
Windows 10
Windows supports the traditional Gannen prefix for the period from 1 May to 31 December without all the fuss and bother needed in Excel.
Windows 10 May 2019 update (1903) has Gannen support on by default.
Previous versions of Windows have Gannen OFF by default, but the Registry can change that.
Gannen ON: [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Control\Nls\Calendars\Japanese] change the InitialEraYear registry key to “元年”.
Gannen OFF: set the InitialEraYear registry key to “1年”.
Show Japanese Imperial Era dates in Excel and why it’s wrong
Converting Excel dates to text and vice-versa