Results 1 to 10 of 10

Conversion of Gregorian date into Islamic date

This is a discussion on Conversion of Gregorian date into Islamic date within the Excel Questions forums, part of the Question Forums category; Can soembody tell me if it is possible to convert Gregorian date into Islamic date by using a formula or ...

  1. #1
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    949

    Default Conversion of Gregorian date into Islamic date

    Can soembody tell me if it is possible to convert Gregorian date into Islamic date by using a formula or VB code in excel? If yes, what is it?
    Thanks in advance.

  2. #2
    Board Regular
    Join Date
    Dec 2006
    Location
    Chicago
    Posts
    128

    Default Re: Conversion of Gregorian date into Islamic date

    I know what Gregorian dates are, but what is an Islamic date?

  3. #3
    Board Regular
    Join Date
    Dec 2006
    Location
    Chicago
    Posts
    128

    Default Re: Conversion of Gregorian date into Islamic date

    After a bit of googling it seems that the Islamic date you referred to is the same as a Hijri date.

    To setup this all you will need to do is change the format of that cell to a custom format

    In the 'Type' field of the 'Format Cells' window that pops up type: B2ddd/mmm/yyy

    the B2 in front of the format will make it the Islamic date format. switch the ddd/mmm/yyy order around to suit your needs, and if you want the date to appear in numbers instead of words you can just use 2 instead of 3 of the letter in question (so you could do B2dd/mm/yy)

  4. #4
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    949

    Default Re: Conversion of Gregorian date into Islamic date

    Thanks a lot. That was simple yet amazing format. I never knew that you cold format like that.
    One more question: When I use format B2dd mmm, it is giving me the month in Arabic language. Is it possible for the month to be shown in English language as "05 Dhil Qadhaa" instead of
    05 ذو القعدة

    If it is not possible, then I am happy for it to be as it is.

  5. #5
    Board Regular
    Join Date
    Dec 2006
    Location
    Chicago
    Posts
    128

    Default Re: Conversion of Gregorian date into Islamic date

    Unfortunatly unless someone else knows of a way i am only aware of getting it to display words in that format, not in english.

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default Re: Conversion of Gregorian date into Islamic date

    Not too easy to do. A lot of problems between the two systems.

    Today: 2 / 11 / 2008, is: Sunday 4 Thw al-Qi`dah 1429 A.H. +/- one day on the lunar calendar as a "Hijri " date or: 3 Dhu al-Qada 1429 +/- one day, by some other reckonings. No lunar dates are truly convertable!

    The Islamic (Hijri) year consists of twelve (purely lunar) months as words not numbers.
    They are:

    (1) MuHarram, 30 days
    (2) Safar, 29 days
    (3) Raby` al-awal or [Rabi'al-Awwal], 30 days
    (4) Raby` al-THaany or [Rabi'ath-Thani], 29 days
    (5) Jumaada al-awal or [Jumada l-Ula], 30 days
    (6) Jumaada al-THaany or [Jumada t-Tania], 29 days
    (7) Rajab, 30 days
    (8) SHa`baan or [Sha`ban], 29 days
    (9) RamaDHaan or [Ramadan], 30 days
    (10) SHawwal, 29 days
    (11) Thw al-Qi`dah or [Dhu al-Qada or Dhu 'l-Qa`da], 30 days
    (12) Thw al-Hijjah or [Dhu 'l-Hijja], 29 days, but 30 days in years 2, 5, 7, 10,
    13, 16, 18, 21, 24, 26, and 29, for leep years.

    The Islamic calendar is purely lunar, as apposed to solar or some luni-solar, the Muslim (Hijri) year is shorter than the Gregorian year by about 11 days, and months in the Islamic (Hijri) year are not related to any seasons at all, seasons are related to the solar cycle not the lunar ones. Due to this, it is a long cycle date system: a 33 year cycle of lunar months is needed for a month to take a complete turn and fall during the same season again. Do not confuse the month cycle of 33 months with the 30 year leep cycle!

    The beginning of a Hijri month is marked not by the start of a new moon, like other lunar calendars, but by the actual physical sighting of the first new crescent moon. So, due to this; The same Gregorian date may have two to four Hijri equivalent dates according to the place and time of the crecent sighting.

    Islamic leep years; Islamic Astronomers have a fixed a 30 year cycle that has the 2nd, 5th, 7th, 10th, 13th, 16th, 18th, 21st, 24th, 26th, and 29th years, marked as leap years of 355 days, remember that the Islamic day starts at sunset and ends on the next sunset, so time and the moon are both used to fix a date!

    The Hijri was first introduced in 638 CE by the close companion of the Prophet Muhammad and the second Caliph, `Umar ibn Al-Khattab (586-644 CE). As an attempt to rationalize the various systems in use at that time. It was agreed that the most appropriate reference point for the Islamic Calendar was the Hijra (Hijrah, Hegira). The actual starting date for the Calendar "Epoch" was chosen -on the basis of purely lunar years, counting backwards- to be the first day of the first month "1st of Muharram" of the year of the Hijra. The Islamic (Hijri) Calendar -with dates that fall within the Muslim Era- is usually abbreviated AH in Western languages from the Latinized Anno Hejirae "In the year of the Hijra." 1st of Muharram, AH 1 corresponds to Friday July 16th, 622 CE in the Julian Calendar.

    A formula to convert an Islamic date into a Gregorian one is, divide the Hejira date by 33.7, subtract the result from the Hejira date and then add 622 or for an approximate equivalent, add 583 to the Hejira date. Because the Islamic year is a lunar year, it is shorter than the western solar year. Therefore you cannot just add or subtract 622 years for a start date.

    Java Function:
    Code:
    function J2I() {
     var Y = document.calc.Year.value;
     var M = document.calc.Month.value;
     var D = document.calc.Day.value;
     if (D>=32) {
      alert("Gregorian and Julian months never have more than 31 days.\n Try again.");
      document.calc.Day.value="";
      document.calc.Day.focus();
      return; 
     }
     else if ( (D==31)&&( (M==2)||(M==4)||(M==6)||(M==7)||(M==11) ) ){
      alert("This month doesn't have 31 days. \n Try again.");
      document.calc.Day.value=" ";
      document.calc.Day.focus();
      return;
     }
     else if ((M==2)&&(D==30)) {
      alert("February never has 30 days. \n Try again.");
      document.calc.Day.value=" ";
      document.calc.Day.focus();
      return;
     } 
     else if ((M==2) && (D==29)){ //check for leap years
          var Leap = false;
          if (Y%4==0) {Leap = true};
          if ((Y%100==0) && (Y%400>0)) {Leap = false};
          if (Y%4000==0) {Leap = false};
          if (Leap==false) {
           alert(Y + " is not a leap year in the Gregorian calendar. Please enter a real date.");
           document.calc.Day.value=" ";
        document.calc.Day.focus();
        return;
          }
        }
     var G = document.calc.GorJ[0].checked; 
     document.calc.IslamOut.value= GregOrJul2Islamic(D,M,Y,G);
    }
    function I2J() {
     var Y = document.Icalc.IYear.value;
     var M = document.Icalc.IMonth.value;
     var D = document.Icalc.IDay.value;
     if (D>30) {
      alert("Islamic months never have more than 30 days.\n Try again.");
      document.Icalc.IDay.value=" ";
      document.Icalc.IDay.focus();
      return; 
     }
     else if ( (D==30)&&(M<12)&&((M%2)==0) ){
      alert("This month doesn't have 30 days. \n Try again.");
      document.Icalc.IDay.value="";
      document.Icalc.IDay.focus();
      return;
     }
     var G = document.Icalc.GorJ[0].checked; 
     document.Icalc.GregOut.value = Islamic2GregOrJul(D,M,Y,G);
    }
    So, given the month table above a conversion table could be built to convert the word month to the number month or back again!
    Last edited by Joe Was; Nov 2nd, 2008 at 07:39 PM.
    JSW: Try and try again: "The way of the Coder!"

  7. #7
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    949

    Default Re: Conversion of Gregorian date into Islamic date

    Thanks a lot guys. You are a great help.

  8. #8
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,971

    Default Re: Conversion of Gregorian date into Islamic date

    Hello asad,

    I note what Joe says about inexact conversions, but assuming that the Hijri date given by Atwork's suggested formatting is correct, you can use a formula which would convert to that format, and then use a lookup table to convert the Arabic month name to an English language month name, see below:

    Note that E2:E13 contains the Arabic month names but obviously they don't display properly in the spreadsheet snapshot, see table below that for what they actually appear as in my spreadsheet:





    ******** ******************** ************************************************************************>
    Microsoft Excel - hijri_dates.xls___Running: 11.0 : OS =
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =




    A
    B
    C
    D
    E
    F
    G
    1
    Gregorian DateHijri DateConversion Table
    2
    01-November-200803 Dhu al-Qi'dah????Muharram
    3
    03-December-200805 Dhu al-Hijjah???Safar
    4
    04-January-200908 Muharram???? ?????Rabi' I
    5
    05-February-200910 Safar???? ??????Rabi' al Thani
    6
    09-March-200913 Rabi' I????? ??????Jumada I
    7
    10-April-200915 Rabi' al Thani????? ???????Jumada al-akhir
    8
    12-May-200918 Jumada I???Rajab
    9
    13-June-200920 Jumada al-akhir?????Sha'aban
    10
    15-July-200923 Rajab?????Ramadan
    11
    16-August-200925 Sha'aban????Shawwal
    12
    17-September-200928 Ramadan?? ??????Dhu al-Qi'dah
    13
    18-October-200929 Shawwal?? ?????Dhu al-Hijjah
    14
    Sheet2

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Formula in B2 copied down

    =TEXT(A2,"B2dd ")&VLOOKUP(TEXT(A2,"B2mmm"),E$2:F$13,2,0)

    Table in E2:F13 actually looks like this in my spreadsheet. I got the month names in there using formulas like =TEXT(TODAY(),"B2mmm")

    محرم Muharram
    صفر Safar
    ربيع الاول Rabī' I
    ربيع الثاني Rabī' al Thānī
    جمادى الاولى Jumādā I
    جمادى الثانية Jumādā al-akhir
    رجب Rajab
    شعبان Sha'abān
    رمضان Ramadan
    شوال Shawwal
    ذو القعدة Dhu al-Qi'dah
    ذو الحجة Dhu al-Hijjah

    Apologies if I got any of the month names wrong, you can change as required......
    Last edited by barry houdini; Nov 2nd, 2008 at 08:20 PM.

  9. #9
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,971

    Default Re: Conversion of Gregorian date into Islamic date

    D'oh!!

    Of course I realised too late that you don't need to use the Arabic month name at all, you can just convert the month number to the English language month name (as Joe suggested), i.e. as per this sample:



    ******** ******************** ************************************************************************>
    Microsoft Excel - hijri_dates.xls___Running: 11.0 : OS =
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =


    A
    B
    C
    D
    E
    F
    1
    Gregorian DateHijri DateMonths
    2
    01-November-200803 Dhu al-Qi'dahMuharram
    3
    03-December-200805 Dhu al-HijjahSafar
    4
    04-January-200908 MuharramRabi' I
    5
    05-February-200910 SafarRabi' al Thani
    6
    09-March-200913 Rabi' IJumada I
    7
    10-April-200915 Rabi' al ThaniJumada al-akhir
    8
    12-May-200918 Jumada IRajab
    9
    13-June-200920 Jumada al-akhirSha'aban
    10
    15-July-200923 RajabRamadan
    11
    16-August-200925 Sha'abanShawwal
    12
    17-September-200928 RamadanDhu al-Qi'dah
    13
    18-October-200929 ShawwalDhu al-Hijjah
    14
    Sheet2

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    formula in B2 copied down is

    =TEXT(A2,"B2dd ")&INDEX(E$2:E$13,TEXT(A2,"B2m"))

    or you can do without the month list completely if you include it in the formula, i.e.

    =TEXT(A2,"B2dd ")&INDEX({"Muharram";"Safar";"Rabī' I";"Rabī' al Thānī";"Jumādā I";"Jumādā al-akhir";"Rajab";"Sha'abān";"Ramadan";"Shawwal";"Dhu al-Qi'dah";"Dhu al-Hijjah"},TEXT(A2,"B2m"))

  10. #10
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    949

    Default Re: Conversion of Gregorian date into Islamic date

    I am very very thankful to you for this. This is a great favour to me that you have done. THANK YOU AGAIN.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com