Excel cannot read user input through drop-down list as a Hijri date!

[SA_Banker]

Respected Members,

First of all, I want to thank everyone contributed to this forum. So much I learned for you!

Hope you can help me on below issue

I searched on the net and here in the forum and found several suggested solutions. Yet, none of it worked for me!

When I use function today() and format it as a hijri date (Saudi Arabia), it converts the date from Gregorian to Hijri successfully.

An issue showed up when I created 3 drop-down lists which are Year, Month and Day. The 3 lists read from sources I created. When a user picks from the 3 lists a date, a cell linked to the output gets updated. The problem is that Excel cannot recognize that the date in linked cell is Hijri and convert it into its corresponding Gregorian date. it gets me totally wrong date. I tried Several suggested solutions such as preceding the assumed date with b1 or a but no success.

[TABLE="width: 272"]
<TBODY>[TR]
[TD="colspan: 3"]Pick Date</SPAN>
[/TD]
[/TR]
[TR]
[TD]1433</SPAN>
[/TD]
[TD]02</SPAN>
[/TD]
[TD]05</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Today</SPAN>
[/TD]
[/TR]
[TR]
[TD="colspan: 3"]1435-02-05</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Output</SPAN>
[/TD]
[/TR]
[TR]
[TD="colspan: 3"]29/09/2794</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


Output=TEXT(DATE(E2,F2,G2),"b2dd/mm/yyyy")

Where E2,F2 and G2 are the Picked date (user chosen from predefined lists)



Successfully doing that, I then convert it to Gregorian and do some calculations.

I just need Excel to accept Hijri Date as an input. For your reference, I attached a file

Excel 2010 - Windows 7 Professional

Thank you in advance!
</SPAN>

 

[SA_Banker]

I will try it now. The file will be distributed by email. Do the users have to do the same thing if they ever had to?

 

[Andrew Poulsom]

The formula will work only if the use can enter a Hijri date by preceding it with the letter a.

 

[SA_Banker]

I mean change the registry. Do the users have to do it if they faced same problem?
or it will be fine if i changed the registry as instructed before distribution?

 

[Andrew Poulsom]

The formula will work only if the user can enter a Hijri date by preceding it with the letter a. That may involve changing the registry.