Hijra Date into Gregorian

[Ahmad.Khattab]

Hi there,

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

a formula or VB

any help

 

[Andrew Poulsom]

You can use a formula like:

=("a"&A1)+0

 

[Ahmad.Khattab]

Thanks Andrew for your great help

 

[Ahmad.Khattab]

Some cells return FALSE as a result of ISNUMBER() and also return #VALUE when I used =("a"&A1) +0
any advice

 

[Andrew Poulsom]

Can you give some examples please?

 

[alone]

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

Andrew can i ask you ?
1) why use ==> For Each Cell In Rng.Cells not ===> For Each Cell In Rng without .Cells
2) what is this command mean DoEvents

I'm sorry for my intervention

 

[Ahmad.Khattab]

<table border="0" cellpadding="0" cellspacing="0" width="333"><col style="width: 64pt;" width="85"> <col style="width: 98pt;" width="130"> <col style="width: 89pt;" width="118"> <tbody><tr style="height: 39pt;" height="52"> <td class="xl68" style="height: 39pt; width: 64pt;" align="left" height="52" width="85">31/04/1428</td> <td style="width: 98pt;" align="center" width="130">FALSE</td> <td class="xl70" style="width: 89pt;" align="center" width="118">#VALUE!</td> </tr> <tr style="height: 38.25pt;" height="51"> <td class="xl68" style="height: 38.25pt; border-top: medium none;" align="left" height="51">24/05/1426</td> <td align="center">FALSE</td> <td class="xl70" align="right">01-07-05</td> </tr> </tbody></table>

 

[Andrew Poulsom]

There are only 29 days in April in the Hijri calendar.

 

[Ahmad.Khattab]

Hi Andrew

Sorry for the confusion

I know the reason as the date was invalid (Non of Hijra months has 31 days)

 

[Andrew Poulsom]

Andrew can i ask you ?
1) why use ==> For Each Cell In Rng.Cells not ===> For Each Cell In Rng without .Cells
2) what is this command mean DoEvents

I'm sorry for my intervention

1) Just to be explicit. It works without it.
2) DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent. Without it Sendkeys isn't processed.

 

[hanseljose]

I have created a excel file with macro which will convert the Hijri date to Grogorian . If anyone need this file , please mail to me hanseljose@gmail.com