We have updated Excel to recognize the new Japanese era, which began on May 1, 2019. This included creating the Gannen number format (Japanese year1), and you can now natively edit and save your Excel workbooks with this format.
To apply the Gannen number format, select the cell or cells to be formatted and press Ctrl+1. In the Format Cells dialog, choose Date from the Number tab. Select Japanese from the Locale (location) drop-down, and Japanese Emperor Reign from the Calendar type drop-down. Make sure that the Use Gannen to display 1st year check box is checked, then press OK.
Notes:
-
We are in the process of rolling this out to Microsoft 365 users. For Desktop Excel, it is available with the Current Channel Version 1909 (Build 12026.20320). We are working on enabling this feature on other endpoints (Mac, iOS, and Android) as well in the near future.
-
If you are not on the Current Channel (Excel 2013, Excel 2016, Excel 2019, Microsoft 365 Semi-Annual Enterprise Channel (Preview) or Semi-Annual Enterprise Channel), or if you have Excel spreadsheets with formulas, macros or custom formats that depend on Japanese era representation, then you can use the custom formatting options for Gannen date display discussed below.
-
To give us feedback around how well the Japanese era experience is working in Excel, or to report any issues, please go to Excel > File > Feedback and use the Send a Smile/Frown/Suggestion options.
Displaying Gannen with a custom number format when using an older version of Excel
-
Select the cell or range that requires formatting.
-
Press CTRL+1 to launch the Format Cells dialog.
-
Choose the Number tab, then select the Custom option from the Category section.
-
Copy and paste the following string into the Type text box:
[<=43585][$-ja-JP]ggge"?"m"?"d"?";[>=43831]ggge"?"m"?"d"?";ggg"??"m"?"d"?"
-
Press OK to apply the format to the selected cells.
In the following example, this is how the date was displayed before applying the custom number format:
This the result after applying the custom number format:
Note that the output is in Custom format, which means that some text functions will not work if you reference it. However, many date functions like NETWORKDAYS() will work.
Displaying Gannen with a text function
It is also possible to display the Gannen date format using a text function.
In the example below, cells A5:A13 contain date values, and cell A2 contains the format string used in the previous example:
[<=43585][$-ja-JP]ggge"?"m"?"d"?";[>=43831]ggge"?"m"?"d"?";ggg"??"m"?"d"?"
The formula =TEXT(A5,$A$2) has been entered into cell C5, and copied down to cells C5:C13.
Note the output is in General format, which means many date and text functions will not work if you reference those cells.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
