Forum Discussion

Enas L's avatar
Enas L
Copper Contributor
Nov 27, 2023

extracting the name of the Hijri month from the Hijri date problem

Hi

I have a problem in extracting the name of the Hijri month from the Hijri date for the last two days of each Hijri month. I used the function =IF(ISBLANK([@date]);" ";TEXT([@date];"b2mmmm")) but it makes an error Extracting the month for the 29th and 30th of the Hijri month in most months, such as: Rabi` Thani, Jumada al-Awwal, Jumada al-Thani, Rajab...etc.

 

I need help to fix this problem..

Excel
Formulas and Functions
office 365
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Dec 01, 2023

    Enas L 

    It looks like you are experiencing issues with the extraction of the Hijri month name for the last two days of each Hijri month in Excel 365. The error you are encountering may be due to the fact that the TEXT function is not handling the 29th and 30th days of some months correctly.

    To address this issue, you can use a combination of the CHOOSE and MONTH functions to extract the month name. Here is a modified formula:

    =IF(ISBLANK([@date), " ", CHOOSE(MATCH(MONTH([@date]),{1,2,3,4,5,6,7,8,9,10,11,12},0), "Muharram", "Safar", "Rabi` al-Awwal", "Rabi` al-Thani", "Jumada al-Awwal", "Jumada al-Thani", "Rajab", "Sha`ban", "Ramadan", "Shawwal", "Dhu al-Qi`dah", "Dhu al-Hijjah"))

    In this formula:

    • MONTH([@date]) extracts the month number from the date.
    • MATCH(...) is used to find the position of the month number in the array {1,2,3,4,5,6,7,8,9,10,11,12}.
    • CHOOSE(...) returns the corresponding Hijri month name based on the position found.

    This formula should handle the extraction of the Hijri month name correctly for the 29th and 30th days of each Hijri month.

    Make sure to adjust the formula according to your specific data structure and requirements.

    In the Islamic or Hijri calendar, months can have either 29 or 30 days. The Hijri calendar is a lunar calendar, and the length of a month is determined by the sighting of the new moon. The basic principle is that a month is either 29 days or 30 days, and the determination is based on the observation of the crescent moon.

    NOTE: My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together. Maybe it will help you further in your project, if not please just ignore it.

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

      • SnowMan55's avatar
        SnowMan55
        Bronze Contributor
        Dec 07, 2023

        Enas L 

        I have a partial understanding of your Islamic month problem.  The Excel function MONTH returns the month number on the Gregorian calendar, even if your cell is formatted to display a Hijri date.  So your formula in column E should use different code to calculate the Islamic month number (probably using a combination of the TEXT and VALUE functions), and then do the conversion to romanized month name.  Try this:

        =IF(ISBLANK([@date]), " ", CHOOSE( VALUE( TEXT([@date,"b2m") ), "Muharram", "Safar", "Rabi' al-Awwal", "Rabi' al-Thani", "Jumada al-Awwal", "Jumada al-Thani", "Rajab", "Sha'ban", "Ramadan", "Shawwal", "Dhu al-Qadah", "Dhu al-Hijjah" ) )

        I have not tested that, but I used similar formulas in columns F and K in the attached workbook.  The Islamic month names in the formulas are copied from an English-language Wikipedia article; I see that at least one of yours is somewhat different.


        I do not understand details of the meaning of the "b2" prefix you are using, nor the meaning of the "[$-,106]" prefix that SergeiBaklan used in this post.  (Neither seems to be a language code or an LCID.  I cannot find Microsoft documentation on them.)  Perhaps you will provide a little more information on the "b2", to educate me.


        Your sample dates in column C include some dates (for example, 1445-02-30) that do not exist on the Tabular Islamic calendar, according to the "Kuwaiti algorithm".  See the attached workbook for generated sequential dates.  Maybe your source contains observation-based dates instead of rule-based dates.  If so, someone else may be able to make a recommendation.

         

Resources