Communiquez avec les autres et partagez vos connaissances professionnelles

Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.

Suivre

In Excel: How to convert Hijri Date to Greg and vice versa?

user-image
Question ajoutée par Joshy Joy , Chief Accountant , Gulf Mahmal Support Services Co
Date de publication: 2017/07/16
Firas Shahadi
par Firas Shahadi , Human Resources Director , SAUDI UNION

You can use this Function

Sub HijToGre(hijCtl As TextBox, gerCtl As TextBox)

    

    Dim SavedCal As Integer

    Dim d As Date

    Dim s, sFrmName As String

    Dim frm As Form

    

    sFrmName = Forms!frmEmp.tbEmp.Pages(Forms!frmEmp.tbEmp).Name

    

    If sFrmName = "pgMOHBLS" Then

        Set frm = Forms!frmEmp!Licenses.Form

    ElseIf sFrmName = "pgGeneral" Then

        Set frm = Forms!frmEmp

    End If

    

    If hijCtl.Text <> "" Then

        'save the old calendar

        SavedCal = Calendar

        '

        VBA.Calendar = 1

        d = Format(CDate(hijCtl.Text), "dd/mm/yyyy")

        VBA.Calendar = 0

        s = DateSerial(year(d), Month(d), Day(d))

        frm(gerCtl.Name) = s

        VBA.Calendar = SavedCal

    End If

 

End Sub

You can also convert Gregorian to Hijri by using b1 in the cell format for the cell in the cell format 

like below

b1dd/mm/yyyy  if this doesn't work check 

b2dd/mm/yyyy

Joshy Joy
par Joshy Joy , Chief Accountant , Gulf Mahmal Support Services Co

=IFERROR(greg_date(Hijri Cells),"No Data")

 

and Macro is.........

 

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

 

More Questions Like This