Convert date locale in google sheet from Gregorian calendar to Jalali calendar

Question

I'm wondered if it's possible in google sheet to convert Gregorian calendar to jalali using a function. In fact, I have some date such as :

February 20, 2021 4:30 AM

I need to display this date in it's front cell in Jalali format (without time) which is :

1399/12/02 

or:

Esfand 02, 1399

As I've mentioned, I need both c Gregorian and Jalali calendars in same sheet in diffrent cells. Is it possible ? Thanks

Answer 1

You can create your own custom function in Apps Script and later use it in Google Sheets.

You will have to start by going to Tools > Script editor.

Afterwards, you can apply the method used by AmirFo here and create a function like this:

function CONVERT_DATE(date) {
  let jalaliFormat = new Date(date).toLocaleDateString('fa-IR').replace(/([۰-۹])/g, token => String.fromCharCode(token.charCodeAt(0) - 1728));
  return jalaliFormat;
}

To make use of this formula, simply type it in your Google Sheets as a regular formula by calling =CONVERT_DATE.

Before

After

Note

Please check your appsscript.json file to make sure you have the correct timezone there, otherwise you might be getting a date +/-1 days.

Reference

• Custom Functions in Google Sheets;

• How to change Gregorian date to Persian date in JavaScript?.