Hijri Gregorian Date Conversion

W

wolflow

New Member
Joined
Apr 23, 2015
Messages
13
Found very useful code to convert Hiiri to Gregorian dates on http://www.islamicsoftware.org/
I found another function which calculates date of Eid Al Adha as a Hijri date.

The two functions aside work fine, however if I try to use the code to convert the Hijri date of Eid Al Adha to a Gregorian date it causes Excel to crash.

=greg_date(12/10/1436) works fine
=Eid_Al_Adha_Hijri(2015) works fine
=gregdate(Eid_Al_Adha_Hijri(2015)) causes Excel to crash

My Excel knowledge is insufficient to solve this issue, your help would be highly appreciated.

Option Base 1

' Source: http://www.islamicsoftware.org/hijridates/hijridates.html

Function isleap(n) As Boolean
isleap = ((n Mod 4 = 0) And (n Mod 400 <> 0))
End Function

Function isLeapH(n) As Boolean
isLeapH = (n = 3 Or n = 5 Or n = 8)
End Function

Function FindYear(n)
'Returns number of whole years elapsed in current cycle
YearFinder = Array(354, 708, 1063, 1417, 1772, 2126, 2480, 2835)
For i = 1 To 8
If n <= YearFinder(i) Then
FindYear = i
Exit For
End If
Next i
End Function

Function FindMonth(n, leap)
'Returns number of whole months elapsed in current year
MonthFinderL = Array(30, 59, 89, 118, 148, 177, 207, 236, 266, 296, 325, 355)
MonthFinder = Array(29, 59, 88, 118, 147, 177, 206, 236, 265, 295, 324, 354)
'would't let me make these two public!
If leap Then
For i = 1 To 12
If n <= MonthFinderL(i) Then
FindMonth = i
Exit For
End If
Next i
Else
For i = 1 To 12
If n <= MonthFinder(i) Then
FindMonth = i
Exit For
End If
Next i
End If
End Function

Function HijriDate(dat As Long) As String
Hstart = 1324
Cstart = CLng(#2/24/1906#) 'Corresponds to 1 Muharram 1324
DCycle = 2835
YearFinder = Array(354, 708, 1063, 1417, 1772, 2126, 2480, 2835)
MonthFinderL = Array(30, 59, 89, 118, 148, 177, 207, 236, 266, 296, 325, 355)
MonthFinder = Array(29, 59, 88, 118, 147, 177, 206, 236, 265, 295, 324, 354)
elp = dat - Cstart
ncycles = elp \ DCycle 'Number of elapsed cycles
ndays_thiscycle = elp Mod DCycle
If ndays_thiscycle = 0 Then 'Last day of the cycle
hyr = Hstart + ncycles * 8
HijriDate = "12/30/" & hyr
Exit Function
End If
nyear = FindYear(ndays_thiscycle) 'This year in current cycle
leapH = isLeapH(nyear)
If nyear = 1 Then
ndays_thisyear = ndays_thiscycle
Else
ndays_thisyear = ndays_thiscycle - YearFinder(nyear - 1)
End If
months = FindMonth(ndays_thisyear, leapH) 'This month in current year
If months = 1 Then
daysinmonths = 0 'Days in preceding months
ElseIf leapH Then
daysinmonths = MonthFinderL(months - 1)
Else
daysinmonths = MonthFinder(months - 1)
End If
nDays = ndays_thisyear - daysinmonths
hyr = Hstart + ncycles * 8 + nyear - 1
Debug.Print dat, ncycles, ndays_thiscycle
Debug.Print nyear, leapH
Debug.Print ndays_thisyear, months, daysinmonths
HijriDate = months & "/" & nDays & "/" & hyr
End Function
Sub convert_month()
Dim a(31)
last_day = Array(31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
s = InputBox("enter month and year in the form mm/yyyy:")
y = CInt(Right(s, 4))
m = CInt(Left(s, 2))
d = DateSerial(y, m, 1)
L = last_day(m)
For i = 1 To L
a(i) = HijriDate(d + i - 1)
Debug.Print i, a(i)
Next i
End Sub
Function greg_date(hdat As String) As Date
YearFinder = Array(354, 708, 1063, 1417, 1772, 2126, 2480, 2835)
MonthFinderL = Array(30, 59, 89, 118, 148, 177, 207, 236, 266, 296, 325, 355)
MonthFinder = Array(29, 59, 88, 118, 147, 177, 206, 236, 265, 295, 324, 354)
Cstart = CLng(#2/24/1906#) 'Corresponds to 1 Muharram 1324
Hstart = 1324
DCycle = 2835
'parse s to produce hmonth, hday, hyear
i = InStr(hdat, "/")
hmonth = CInt(Left(hdat, i - 1))
j = InStr(i + 1, hdat, "/")
hday = CInt(Mid(hdat, i + 1, j - i - 1))
hyear = CInt(Right(hdat, Len(hdat) - j))
elapsed_years = hyear - Hstart
ncycles = elapsed_years \ 8
nyear = elapsed_years Mod 8
If nyear = 0 Then
days_thiscycle = 0
Else
days_thiscycle = YearFinder(nyear)
End If
leap = isLeapH(nyear)
If hmonth = 1 Then
days_thisyear = hday
Else
If leap Then
days_thisyear = MonthFinderL(hmonth - 1) + hday
Else
days_thisyear = MonthFinder(hmonth - 1) + hday
End If
End If
days_thiscycle = days_thiscycle + days_thisyear
greg_date = Cstart - 1 + ncycles * DCycle + days_thiscycle
End Function

Public Function Eid_Al_Adha_Hijri(GregYear As Integer) As String
' http://en.wikipedia.org/wiki/Eid_al-Adha
' 10th day of 12th month -> SEE LAST LINE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' Need this function to convert other Hijri dates as well......
' So need to be able to change Hijri month & day

' Feast of the Sacrifice
' Latter of two Eids

' 1 = Muharram
' 2 = Safar"
' 3 = Rabi’ al-awwal(Rabi’ I)
' 4 = Rabi’ al-thani(Rabi’ II)
' 5 = Jumada al-awwal(Jumada I)
' 6 = Jumada al-thani(Jumada II)
' 7 = Rajab
' 8 = Sha'ban
' 9 = Ramadan
' 10 = Shawwal
' 11 = Dhu al-Qi'dah
' 12 = Dhu al-Hijjah

Hstart = 1324
Cstart = CLng(#2/24/1906#) 'Corresponds to 1 Muharram 1324
DCycle = 2835
YearFinder = Array(354, 708, 1063, 1417, 1772, 2126, 2480, 2835)
MonthFinderL = Array(30, 59, 89, 118, 148, 177, 207, 236, 266, 296, 325, 355)
MonthFinder = Array(29, 59, 88, 118, 147, 177, 206, 236, 265, 295, 324, 354)
elp = DateSerial(GregYear, 1, 1) - Cstart
ncycles = elp \ DCycle 'Number of elapsed cycles
ndays_thiscycle = elp Mod DCycle
If ndays_thiscycle = 0 Then 'Last day of the cycle
hyr = Hstart + ncycles * 8
Eid_Al_Adha_Hijri = "12/30/" & hyr
Exit Function
End If
nyear = FindYear(ndays_thiscycle) 'This year in current cycle
leapH = isLeapH(nyear)
If nyear = 1 Then
ndays_thisyear = ndays_thiscycle
Else
ndays_thisyear = ndays_thiscycle - YearFinder(nyear - 1)
End If
months = FindMonth(ndays_thisyear, leapH) 'This month in current year
If months = 1 Then
daysinmonths = 0 'Days in preceding months
ElseIf leapH Then
daysinmonths = MonthFinderL(months - 1)
Else
daysinmonths = MonthFinder(months - 1)
End If
nDays = ndays_thisyear - daysinmonths + 1
hyr = Hstart + ncycles * 8 + nyear - 1
Debug.Print dat, ncycles, ndays_thiscycle
Debug.Print nyear, leapH
Debug.Print ndays_thisyear, months, daysinmonths
Eid_Al_Adha_Hijri = "12/10/" & hyr

End Function
 
Sort by date Sort by votes
Hi Rick,
That won't work unfortunately.
I would like to be able to use this function for other Hijri dates as well, that's why I specified month and day.
Since Hijri year is approx 11 days shorter than Gregorian year just subtracting a number of years from Gregorian year won't work.
Kind regards,
Jurgen
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
wolflow said:
Hi Rick,
That won't work unfortunately.
I would like to be able to use this function for other Hijri dates as well, that's why I specified month and day.
Since Hijri year is approx 11 days shorter than Gregorian year just subtracting a number of years from Gregorian year won't work.
Kind regards,
Jurgen
Click to expand...

In that case, I am sorry but I don't understand what you are trying to do.

The routines I posted take into account the differences between the lunar and solar calendars and will convert between them. The answers they get match within a day the dates of the festival you mentioned and they also exactly match the dates in one of the calendar links you provided. That festival occurs nominally on the same Hijri data every year but, it seems, that last minute adjustments can be made.

I know the lunar calendar is 11 days or so shorter than the solar calendar and the functions reflect that.


Confused,
 
Upvote 0
The following returns 05/07/2016, is it correct?

Code: 
Public Function EidAlFitr$()
Dim EventDate As Date, YearToday As Integer, h As Date, Hijri_Date_1_Jan$
Dim StartShowing%, EndShowing As Integer, Hijri_Month%, Hijri_Day%
YearToday = Year(Now())
StartShowing = 60: EndShowing = 0
Hijri_Month = 10: Hijri_Day = 1
YearFinder = Array(354, 708, 1063, 1417, 1772, 2126, 2480, 2835)
MonthFinderL = Array(30, 59, 89, 118, 148, 177, 207, 236, 266, 296, 325, 355)
MonthFinder = Array(29, 59, 88, 118, 147, 177, 206, 236, 265, 295, 324, 354)
Cstart = CLng(#2/24/1906#) 'Corresponds to 1 Muharram 1324
Hstart = 1324
DCycle = 2835
G = "1/1/" & YearToday                      ' Calculate Hijri year at January First
If Len(G) > 0 Then
    h = CDate(G)
    VBA.Calendar = vbCalHijri
    Hijri_Date_1_Jan = CStr(h)
    VBA.Calendar = vbCalGreg
End If
If Err.Number <> 0 Then Hijri_Date_1_Jan = G
Hijri_Year_1_Jan = Right(Hijri_Date_1_Jan, 4)
' Now calculate actual date in this year
hdat = Hijri_Month & "/" & Hijri_Day & "/" & Hijri_Year_1_Jan   '"10/1/1437"
i = InStr(hdat, "/")                        'parse s to produce hmonth, hday, hyear
hmonth = CInt(Left(hdat, i - 1))
j = InStr(i + 1, hdat, "/")
hday = CInt(Mid(hdat, i + 1, j - i - 1))
Hyear = CInt(Right(hdat, Len(hdat) - j))
elapsed_years = Hyear - Hstart
ncycles = elapsed_years \ 8
nyear = elapsed_years Mod 8
If nyear = 0 Then
    days_thiscycle = 0
Else
    days_thiscycle = YearFinder(nyear - 1)
End If
If nyear = 4 Or nyear = 6 Or nyear = 9 Then leap = True
If hmonth = 1 Then
    days_thisyear = hday
Else
    If leap Then
        days_thisyear = MonthFinderL(hmonth - 2) + hday
    Else
        days_thisyear = MonthFinder(hmonth - 2) + hday
    End If
End If
days_thiscycle = days_thiscycle + days_thisyear
EventDate = Cstart - 1 + ncycles * DCycle + days_thiscycle
EidAlFitr = EventDate
End Function
 
Upvote 0
Hi Rick,
The date 05/07/2016, is correct indeed
Unfortunately I still get "Function call on left-hand side of assignment must return Variant or Object" error using your function.
Kind regards,
Jurgen
 
Upvote 0
How are you using it, with code or as a worksheet formula?

eid.JPG
 
Upvote 0
So, are you saying that this does not work for you?

Excel 2013
ABC
1H2G:1/1/143714/10/2015
2G2H:01/01/201621/03/1437
Sheet2
Cell Formulas
RangeFormula
C1=h2g(B1)
C2=g2h(B2)

Code: 
Function G2H(dtGregDate As Date) As String
    ' returns a date in Hijri format for a given western date
    VBA.Calendar = vbCalHijri
    G2H = dtGregDate
    VBA.Calendar = vbCalGreg
End Function

Function H2G(dtHijDate As String) As Date
    ' returns a Gregorian date in from a string containing a Hijri date
    VBA.Calendar = vbCalHijri
    H2G = dtHijDate
    VBA.Calendar = vbCalGreg
End Function


Regards,
 
Last edited:
Upvote 0
Hi Rick,
Works excellent in two separate functions but could not get it combined in one function to determine current Hijri year at January first and then calculate Gregorian date in this Hijri year using a given Hijri month and day.
Kind regards,
Jurgen
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
How to get rid of Option Base 1
Replies
2
Views
770
Worf
Worf
L
  • Question
Need Correction in Custom Function
Replies
1
Views
532
RickXL
RickXL

Forum statistics

Threads
1,215,344
Messages
6,124,407
Members
449,157
Latest member
mytux

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top