Dateserial equivalent for hijri dates

[YasserKhalil]

Hello everyone
There is the function DateSerial that convert text to dates
I have in range("C1") the text

Please Login or Register  to view this content.

And I used the following line of code to get the date

Please Login or Register  to view this content.

This is for Gregorian dates .. What about Hijrii dates?
If I have this text in C1

Please Login or Register  to view this content.

I need to have the final result in D1 as follows
26/10/1436
and at the same time to be valid date not just a text

Thanks advanced

[YasserKhalil]

ِAny idea about this topic?

[MrShorty]

My first idea is that I know I have seen this question asked before. I do not know the answer myself, but it seems that I have seen formulas and udfs and such out there for converting dates between the Hijri and Gregorian calendars. I put "gregorian to hijri udf excel" into my favorite internet search engine and quickly found these pages that discuss the topic.
https://support.microsoft.com/en-us/kb/871138 says that it only applies to Excel 2003, but mentions a registry edit that claims to allow Excel to recognize Gregorian and Hijri dates.
http://download.cnet.com/Hijri-Grego...-75222365.html is for an add-in that claims to be able to convert between Gregorian and Hijri dates.
http://www.access-programmers.co.uk/...d.php?t=246559 this forum thread presents code for Gregorian to Hijri and Hijri to Gregorian conversion UDFs.

I have not tested any of these, nor am I familiar with the Islamic calendar so I cannot test them. See if any of those help.

[YasserKhalil]

Thanks a lot sir
but I don't need to convert between Gregorian and Hijri ..
I need to convert text formed of 6 numbers like that

Please Login or Register  to view this content.

to Hijri date using UDF .. to have the final result

Please Login or Register  to view this content.

Thanks for help

[MrShorty]

How closely did you look at the links I posted -- especially the third one? I was hoping that, even if the UDF's did not help you with your specific problem, there would be something in the code presented that would be applicable to your specific requirements. Looking at the code more closely, it appears that they are simply using the VBA.calendar property to perform the conversion. Here's the help file for the VBA.calendar property (https://msdn.microsoft.com/en-us/lib...=vs.60%29.aspx ) which has more code and UDF's. One of these UDF's appears to take a string input, selects the desired calendar using the vba.calendar property, then uses the CDate() function to convert the string input to a date in the desired calendar. It also takes and converts the date back to a string, but you may not need that step.

[YasserKhalil]

Thanks for this useful link
I need to create date by splitting the 6 numbers to day / month / Add 14 + plus year

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regardless the conversion process .. I don't need to convert Hijri to Gregorian ..

[MrShorty]

Is there a specific part that you are having trouble with? It seems to me that it should be something like (untested pseudocode):

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very much for this great help
Now I used this and it is OK

Please Login or Register  to view this content.

Is it necessary to include the VBA.Calendar type and reset it at the end ? What is the benefit of these procdures?

[MrShorty]

As explained in the help file for the calendar property, the calendar property allows you to test and/or choose which calendar (Gregorian or Hijri) you want to use. Whether you need to include the VBA.calendar=1 at the beginning and/or VBA.calendar=0 at the end probably depends a lot on what this and other projects are doing.

If, like me, you only use the default Gregorian calendar, you never need to use this or even be aware of it (I was not aware of the VBA.calendar property before this discussion. Everything I know about it is present in the links I have posted).

If you only need the Hijri calendar, and never need the Gregorian, then you can perhaps have an auto open procedure of some kind that will set VBA.calendar to 1 and never worry about it.

If you have some procedures that use the Hijri calendar and some that use the Gregorian calendar, it seems important to use the VBA.calendar property at the beginning and/or end of each procedure to test and/or set the desired calendar type so that the procedure will use the proper calendar, and leave the calendar setting at your desired default when the procedure ends.

Are you certain that the above code is doing exactly what you asked for? When I execute, I get another text string back, not a date. Your stated goal at the beginning was to convert a text string that represents a date (in Hijri) into a date serial number, if I understood correctly. It does not appear to me that this procedure is doing that.

[YasserKhalil]

Thanks a lot for this useful illustration

As for the function it works for me and I tested the result using IsDate function to make sure it is a date and it returns True
Regards