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

SA_Banker

New Member
Joined
Dec 8, 2013
Messages
7
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.

Pick Date</SPAN>
1433</SPAN>
02</SPAN>
05</SPAN>
Today</SPAN>
1435-02-05</SPAN>
Output</SPAN>
29/09/2794</SPAN>

<TBODY>
</TBODY>


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

New Member
Joined
Dec 8, 2013
Messages
7
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
a1433-02-05</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
the format is Gregorian, yet it still shows as
a1433-02-05

<TBODY>
</TBODY>
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Can you enter a Hijri Date by preceding it with the letter a, eg a05/02/1435 or a1435-02-05?
 

SA_Banker

New Member
Joined
Dec 8, 2013
Messages
7
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
 

Forum statistics

Threads
1,082,151
Messages
5,363,434
Members
400,737
Latest member
vipamuk

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top