Do one of the following:
-
Create a custom number format.
-
On the Format menu, click Cells, and then click the Number tab.
-
Select Custom from the list of number categories.
-
Select a number format code as a starting point for your date format.
-
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.
Format dates using the Buddhist calendar
-
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.
- On the Format menu, click Cells, and then click the Number tab.
- Select Custom from the list of number categories.
- 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.
-