• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

hijri date conditional formatting

G

ganeshm

Member
hi ...

How to use conditional formatting for hijri dates.
The formula i used for Gregorian date is

=$C$10-TODAY()<7.

How do u use it with the hijri date.

Regards,
ganeshm
 
Good day ganeshm

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
 
You must log in or register to reply here.
Back
Top