Hijra Date into Gregorian

[Ahmad.Khattab]

Hi there,

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

a formula or VB

any help

 

[Claymationator]

Welcome to the board!

1. On the Format menu, click Cells, and then click the Number tab.
2. Select Custom from the list of categories.
3. In the Type box, do one of the following:

Gregorian Format - "B1dd/mm/yy" - To display dates using the Gregorian calendar, regardless of the Regional Options or Regional Settings of the Microsoft Windows Control Panel setting, type B1 before the date format.

Hijri Format - "B2dd/mm/yy" – to type the date in Gregorian format but it will be interpreted and displayed as Hijri date.

Hope that helps.

 

[Ahmad.Khattab]

1st, thanks a lot for your quick reply but i tried that already but no useful

the excel sheet that i have was made by Excel 2003 and I tried to load it using 2007 & 2010 but still it displayed the same Hihra dates

29/08/1432 for 30-07-2011
29/08/1432 for 29-04-2009

i have around 10 columns contain more than 1000 dates that i need to convert
i tried change the date format in these columns but it keep display the dates in Hijra

 

[Ahmad.Khattab]

I also tried to read these columns in new columns one and display it in the format i want but still not success

 

[Andrew Poulsom]

Are you sure that your dates are serial dates, not text entries that look like dates? You can use the ISNUMBER function to check - it will return TRUE if the cell contains a serial date.

 

[Ahmad.Khattab]

You are right Andrew !!
when i check it , it all gives False

any idea how i can overcome that issue !

 

[Andrew Poulsom]

You can edit the cell and precede the entry with an a, eg a29/08/1432.

 

[Ahmad.Khattab]

Thanks Andrew
however, it works for Cell by Cell but how i can populate it to all cells !! or I have to update all cells one at a time !!

 

[Andrew Poulsom]

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

 

[Ahmad.Khattab]

Thank you very much Andrew
I works fine

by the way, can it be written as a function so i can call from any cell instead of run a macro that change the whole cells ?

it might be more helpful if I want to keep both dates in the sheet