Displaying Hebrew Date

I am trying to format a cell to display the Hebrew date, to do this I have tried:

right click cell

format cells

date

set locale(location) to hebrew

 

however when doing this I get an empty list box with no date formats, if I try an Arabic locale I can see anther box to choose the calendar type and all of them display various date formats, how can I restore the Hebrew date formats?

 

Thank You

 

Question Info


Last updated March 3, 2020 Views 6,687 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

You will need a lot more than a locale change to gain a true Hebrew date. As there is no native function(s) to convert from Gregorian to Hebew (or back), a fairly complicated VBA UDF (User Defined Function) will have to be added to your workbook. There is a fairly good example called DateToHeb() floating around the code sites, but it has the shortcoming of assuming that the date requested is always before sunset. I found a copy here,

CodeS-SourceS
http://files.codes-sources.com/fichier.aspx?id=48737&f=JP_Calendrier+3.5%2fAstroConversions.bas


  • If this proposed solution has resolved your issue(s), please return and mark it as Answered for others to consider.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I did a quick ALT+F11, Paste job. Here is what you can expect from that function,


  • If this proposed solution has resolved your issue(s), please return and mark it as Answered for others to consider.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

but it has the shortcoming of assuming that the date requested is always before sunset.


That is not really a shortcoming as the Jewish day starts at sundown, not at midnight. Here are two links (basically page 1 and page 2) to explain in excrutiating detail everything you could ever want to know about the Hebrew calendar...

http://www.jewfaq.org/calendar.htm

http://www.jewfaq.org/calendr2.htm

The part about the Jewish day starting at sundown and why is in that second link in the 8th paragraph under the section entitled "Calendar Essentials".


NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.
*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

This formula has attempted to simplify the fact that it could be used all around the world in any lattitude. It simulates sunset by calling every day at noon sunset. Anything before noon is the Hebew day converted from Gregorian and everything noon and beyond is considered tomorrow (e.g. after sunset).
  • If this proposed solution has resolved your issue(s), please return and mark it as Answered for others to consider.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I know how to use formulas to do this, I would like to know why it cannot be done using the date format like it can be done with various other non-Gregorian calenders?

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

The only answer I can think of is that Microsoft, for whatever reason, decided not to make Hebrew dates available as a format option.
NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.
*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

On Sun, 12 Sep 2010 19:04:07 +0000, BSagal wrote:
 
>I know how to use formulas to do this, I would like to know why it cannot be done using the date format like it can be done with various other non-Gregorian calenders?
 
Which non-Gregorian calendars can be properly displayed using the Date
Format?
 
 
Ron

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

On Sun, 12 Sep 2010 19:04:07 +0000, BSagal wrote:
 
>I know how to use formulas to do this, I would like to know why it cannot be done using the date format like it can be done with various other non-Gregorian calenders?
 
To clarify what I asked:
 
When I right click on a cell and select format, I do see
Hebrew(Israel) as one of the dropdowns.  But the available formats are
not dates from the Jewish calendar, rather they are dates in the
format of dd-mm-yyyy; and when months are to be "spelled out", as in
dd-mmmm-yyyy, the month is merely transliterated Hebrew of the
English, and not the Jewish months themselves.
 
The days of the week, on the other hand, do seem to be real Hebrew
days, with ddd --> yom aleph  and dddd--> yom rishon  (the days are in
Hebrew)
 
I don't know why you are not seeing the options in your Format Cells
dialog box.
 
Perhaps something messed up with your installation.
 
But try a custom format of:
 
[$-101040D]dddd, mmmm dd yyyy
 
and see if you get the Hebrew and the right-to-left set.
 
Ron

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

On Sun, 12 Sep 2010 19:04:07 +0000, BSagal wrote:
 
>I know how to use formulas to do this, I would like to know why it cannot be done using the date format like it can be done with various other non-Gregorian calenders?
 
One further question:  Do you have a Hebrew font in your system? Maybe
that is a necessity.  There are free one's available.
 
Ron

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

my computer runs in Hebrew and the default calender of the system is the Hebrew lunar calenders

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.