[Tutorial] Converting between Gregorian and Hebrew dates in Google Sheets

So awhile back I made a spreadsheet that converts dates from the Gregorian to the Hebrew calendar. Apparently it made its way onto a Google support thread for people who want to convert dates to the Hebrew calendar in Google sheets! I still get asked about this from time to time, and since I learned recently that you can add custom functions to Google sheets using Google Apps Script I decided to make a script that does it properly. It is pure javascript and does not rely on any external API’s. If you are like I was until not long ago and don’t know how to add custom functions to Google Sheets, here is how:

1. In a new Spreadsheet, go to Extensions > Apps Script. This should open a new window with the following code:

   function myFunction() {

   }

2. Select all the text and paste in my script which can be found here (GitHub Gist) as well as in the comments

3. Save the script and close the window

4. Refresh the spreadsheet

You should now be able to use the Hebrew Calendar functions! This script provides seven functions for Hebrew Calendar conversion:

IS_HEBREW_LEAP_YEAR – Determines if the input is a Hebrew leap year
TO_HEBREW_DATE – Converts the input to a Hebrew date
HEBREW_YEAR – Hebrew year specified by a given date
HEBREW_MONTH – Hebrew month specified by a given date
HEBREW_DAY – Hebrew day specified by a given date
FROM_HEBREW_YEAR_MONTH_DAY – Converts a Hebrew year, month, and day into a date
FROM_HEBREW_DATE – Converts a Hebrew date to a Google Sheets serial date

The autocomplete boxes have more information on the details and usage of each function.

And for those of you who use Microsoft Excel, I have found this script which functions much like mine (I haven’t read too carefully into the details of how that one works, though it looks very similar).

Happy Spreadsheeting! וחג חנוכה שמח!‏ 🕎