Japan era changes and Gannen

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.

Image of the Format Cells dialog on the Number pane with the Gannen date format applied.

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

  1. Select the cell or range that requires formatting.

  2. Press CTRL+1 to launch the Format Cells dialog.

  3. Choose the Number tab, then select the Custom option from the Category section.

  4. Copy and paste the following string into the Type text box:

    [<=43585][$-ja-JP]ggge"?"m"?"d"?";[>=43831]ggge"?"m"?"d"?";ggg"??"m"?"d"?"

    Applying Gannen format with a custom number from Ctrl+1 > Number tab > Custom number.

  5. 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:

    Image with Japanese date applied without Gannen

    This the result after applying the custom number format:

    Image with Gannen applied

    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.

Image of applying Gannen format with the TEXT function: =TEXT(A1,$B$2) where B2 houses the Gannen format string.

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, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×