You have to make a Public function in a module.
Also notice that there are 3 arguments that you need to supply, not just
one.

NickHK

"Abdul"
roups.com...
thanks

How I could use this one as a formula in Excel

for eg.
In the function i posted if i type =DHijri(CellReference) it will
convert Gregorian to Hijri

now if I want to convert Hijri to Gregorian
=ChangeCalenderDate(CellReference) gives #Name! Error

Thanks


News wrote:
Abdul,
This seems to work

Public Function ChangeCalenderDate(InputDate As Date, _
ToHijri As Boolean, _
FormatStr As String) _
As String
Dim OldCalendar As VbCalendar
OldCalendar = Calendar

Calendar = -ToHijri
ChangeCalenderDate = Format(InputDate, FormatStr)
Calendar = OldCalendar

End Function

However according http://www.rabiah.com/convert/, this function gives a
result one day out, although the site does indicate this is possible, but
does not explain why.
If you real the Excel Help on this subject, you will see that you format
cells with either calendar.
As I never use any of this I can't help you as a lot of this depends on
the
system/Excel version in use.

NickHK

"Abdul"
egroups.com...
the following function converts Gregorian Date to Hijri
But when I do the opposite it done work, I mean convert a Hijri date to
Gregorian.

Any Help?!!



Function DHijri(dtGegDate As Date) As String

' returns a date in Hijri format for a given western date
VBA.Calendar = vbCalHijri
DHijri = dtGegDate
VBA.Calendar = vbCalGreg

End Function

Thanks

Abdul