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>

 

[Andrew Poulsom]

Can you enter a Hijri Date by preceding it with the letter a, eg a05/02/1435?

 

[SA_Banker]

Andrew,
Thank you for your prompt replay
I already tried your suggestion and now again. what i do is writing the code ="a"&E2&"-"&F2&"-"&G2 and the result is
[TABLE="width: 111"]
<TBODY>[TR]
[TD="align: left"]a1433-02-05</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
the format is Gregorian, yet it still shows as
[TABLE="width: 111"]
<TBODY>[TR]
[TD="align: left"]a1433-02-05[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Andrew Poulsom]

This works for me:

=("a"&E2&"-"&F2&"-"&G2)+0

 

[SA_Banker]

This works for me:

=("a"&E2&"-"&F2&"-"&G2)+0

Sadly not for me, I got #VALUE!

I appreciate your help and support. Thank you!

 

[Andrew Poulsom]

Can you enter a Hijri Date by preceding it with the letter a, eg a05/02/1435 or a1435-02-05?

 

[SA_Banker]

Can you enter a Hijri Date by preceding it with the letter a, eg a05/02/1435 or a1435-02-05?

I already did but no success
i suggest to email you the sheet to get better insight

 

[Andrew Poulsom]

I mean manually not with a formula.

 

[SA_Banker]

I mean manually not with a formula.

Yes I can i just did it manually and accepted as exactly i inserted manually

 

[Andrew Poulsom]

If I type a1435-02-05 into a cell formatted as general it appears as 05/02/1435 (08/12/2013 in the formula bar). If you can't do that you need to modify the registry as in Post #4 here:

Cell gets formatted as Hijri date (Arabic date)