Hijra Date into Gregorian

[Ahmad.Khattab]

Hi there,

How i can convert Hijra (Islamic) Dates into Gregorian?

a formula or VB

any help

 

[Multamis]

how to convert 1-Moharam-60 AH into christian date?

 

[Multamis]

how to convert 10-Moharam-60 AH into christian date?

 

[jaffar71]

Dear Andrew,
I previously used the tips you gave for converting Hijri Dates to Georgian and it worked fine with the previous version of Excel. Now I'm working on Excel 2010 but these solutions do not seem fit for the latest version. Kindly help me in getting this problem solved.

 

[Andrew Poulsom]

Welcome to MrExcel.

Which tips don't work in Excel 2010 exactly and in what way?

 

[jaffar71]

Putting "a" in front of hijri dates doesn't work in Excel 2010. The new release recognizes the date from 00/01/1900, the dates before this date are shown as FALSE while checking with ISNUMBER formula.

 

[Andrew Poulsom]

It works for me in Excel 2010. If I enter a29/08/1432 in a cell it appears as 29/08/1432 and as 30/07/2011 in the formula bar.

 

[jaffar71]

What do you think, could be the reason at my end. Is it somewhere in Excel Option where I need to correct. I recently have changed my laptop with Excel 2010 installed. Earlier I had Excel 2007, I'm using the same file which worked perfectly in previous version. Now it shows #VALUE error in those cells. Any date before 01/01/1900 is not recognized as number and therefore the hijri date is not working.

 

[Andrew Poulsom]

Maybe you need to add a key in the Registry (take care):

HKCU\Software\Microsoft\Office\14.0\Excel\Options\

Value = xl9_hijri
Value Type = DWORD
Value Data = 0

 

[jaffar71]

Thanks Andrew. Although I did these changes in registry yesterday, yet it was not workable but today morning it seems working now. Probably it need a restart to affect these changes in registry. Thanks again for your usual support.

 

[ris_gabriel]

Sorry, I don't know how to convert all cells at once without a macro. This worked for me:

Sub Test()
    Dim Rng As Range
    Dim Cell As Range
    Set Rng = Range("A1").SpecialCells(xlCellTypeConstants, 2)
    For Each Cell In Rng.Cells
        If Cell.Value Like "##/##/####" Then
            SendKeys "{F2}{HOME}a{ENTER}"
            Cell.Select
            DoEvents
        End If
    Next Cell
End Sub

hi Mr. Andrew with due respect, im amateur to this kind of issue but however if you can help me how to do the basic to start using this code?thank you