How to enter and use Hijri date in Microsoft Excel 2003

Article ID: 871138 - View products that this article applies to.
Notice
Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986
(http://support.microsoft.com/kb/256986/ )
Description of the Microsoft Windows Registry
Expand all | Collapse all

SUMMARY

In Microsoft Excel 2003, to use the date in Hijri Format some settings are needed to be applied.
Back to the top | Give Feedback

MORE INFORMATION

  • The earliest date allowed for calculation is January 1, 1900 (January 1, 1904, if 1904 date system is used).
  • The Excel sheet accepts typing the Gregorian date and it can interpreted it as Hijri date, (according to the setting that you applied in the Cell Format).
  • The user needs to apply special setting before entering the date in Hijri format. because entering wrong date format will cause to have wrong sorting and to get invalid results.
  • Without applying the setting, Excel 2003, will try to handle the Hijri date that you entered as Gregorian date, which is not applicable, because the earliest date allowed for calculation is January 1, 1900 (January 1, 1904, if 1904 date system is used). For example Excel will handle the Hijri date 9/25/1420 (this is Gregorian date 1/1/2000), but it can't handle the Gregorian date 9/25/1420 because is too early than January 1, 1900 (or January 1, 1904, if 1904 date system is used).
Back to the top | Give Feedback

RESOLUTION

The following steps show how we can custom the date in the cells, to be entered as Gregorian format and interpreted in Hijri format, also we will describe how we can enter a date in Hijri format in a cell.

Type a date in Gregorian format and have Excel interpret it as Hijri date:
  1. On the Format menu, click Cells, and then click the Number tab.
  2. From the Type list select the Hijri format that you want to use and press OK, or continue to proceed in the next steps (Step 3)
  3. Select Custom from the list of categories.
  4. In the Type box, do one of the following:
    1. B1dd/mm/yy - 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.
    2. B2dd/mm/yy – to type the date in Gregorian format but it will be interpreted and displayed as Hijri date.
This means that Arabic editing is enabled, to display dates using the Hijri calendar and regardless of the Regional Options or Regional Settings setting, type B2 before the date format code. 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.

Type a date in a cell in Hijri format.
The following method will work only on Arabic Office SKU.
To enable this method for non Arabic SKU’s; you will need to add the following registry key:
HKCU\Software\Microsoft\Office\11.0\Excel\Options\

Value = xl9_hijri
Value Type = DWORD
Value Data = 0


To enter Hijri date, type A or a in front of the date entry
For example, you can 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' Note Your regional settings affect how Excel recognizes the day, month and year parts of the value entered. For example, when using English (United States) regional settings, you should enter the Hijri value as 'a9/25/20'. When using Arabic (Egypt) regional settings, you should enter the Hijri value as 'a25/9/20' Additional keywords
Back to the top | Give Feedback

Properties

Article ID: 871138 - Last Review: August 25, 2008 - Revision: 1.1
APPLIES TO
  • Microsoft Office Excel 2003
Keywords: 
kblangsupparabic kbbidi kbhowto KB871138
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top

Article Translations