Format dates using alternate calendars

Do one of the following:

• Create a custom number format.

1. On the Format menu, click Cells, and then click the Number tab.

2. Select Custom from the list of number categories.

3. Select a number format code as a starting point for your date format.

4. In they Type box, do one of the following:

   • To display dates using the Gregorian calendar, regardless of the Regional Options or Regional Settings of the Microsoft Windows Control Panel setting, type B1 before the date format code— for example, B1dd/mm/yy.

   • When Arabic editing is enabled, to display dates using the Hijri calendar, regardless of the Regional Options or Regional Settings setting, type B2 before the date format code— for example, B2dd/mm/yy.

   Note  It is also possible to format the date by changing the locale selected in the Locale (location) box for the Date category on the Number tab and then selecting a date format in the Type box.

• Enter a date in a cell and have Excel interpret it as a Hijri date instead of Gregorian.

  Type A or a in front of the date entry— for example, enter the short date 'a9/25/20', which will be interpreted as Hijri date 9/25/1420 (this is Gregorian date 1/1/2000). If the cell is unformatted, it will be assigned the number format 'B2m/d/yyyy' and the value will be displayed as 9/25/1420.

• When the Default version of Microsoft Office is set to Thai in the Enabled Languages tab of Microsoft Office Language Settings, do the following:

  • Create a custom number format.

    1. On the Format menu, click Cells, and then click the Number tab.
    2. Select Custom from the list of number categories.
    3. Select a number format that uses the 'b' syntax to represent the year— for example, the syntax 'd/m/bbbb' will format the Gregorian date 1/1/2000 as Thai Buddhist date 1/1/2543.

    Note It is also possible to format the date by changing the locale selected in the Locale (location) box for the Date category on the Number tab and then selecting a date format in the Type box. This format will work regardless of the Default version of Microsoft Office setting in Microsoft Office Language Settings.

  • To enter a date in a cell and have Excel interpret it as a Thai Buddhist date instead of Gregorian, use the B or b prefix during date entry— for example, enter the short date '1/1/b43', which will be interpreted as Thai Buddhist date 1/1/2543. If the cell is unformatted, it will be assigned the number format 'd/m/bb' and the value will be displayed as 1/1/43.