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

user_6218173 0
Viewed 2917

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

google-sheets-formula web-scraping google-sheets date function
2 Answers

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

before formula

After

after using the custom function

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

user_117720350

without scripts you can try:

=ARRAYFORMULA(IFERROR(VLOOKUP(DATEVALUE(A1:A3), {ROW(INDIRECT(
 DATEVALUE("01/01/"&YEAR(TODAY()))&":"&
 DATEVALUE("12/31/"&YEAR(TODAY())))), 
 REGEXREPLACE(REGEXREPLACE(REGEXEXTRACT(QUERY(IMPORTXML(
 "https://calendar.zoznam.sk/persian_calendar-en.php?ly="&YEAR(TODAY()), "//tr"), 
 "select Col2 where Col2 is not null"), 
 ": (.+)"), " ", ", "), "\., ", " ")}, 2, 0)))

enter image description here

update:

=ARRAYFORMULA(IFERROR(VLOOKUP(DATEVALUE(A1:A3), {ROW(INDIRECT(
 DATEVALUE("01/01/"&YEAR(TODAY()))&":"&
 DATEVALUE("31/12/"&YEAR(TODAY())))), 
 REGEXREPLACE(REGEXREPLACE(REGEXEXTRACT(QUERY(IMPORTXML(
 "https://calendar.zoznam.sk/persian_calendar-en.php?ly="&YEAR(TODAY()), "//tr"), 
 "select Col2 where Col2 is not null"), 
 ": (.+)"), " ", ", "), "\., ", " ")}, 2, 0)))

enter image description here

REVERSE:

How to convert Persian (Shamsi) date to Gregorian (Miladi) date with Function Script in Excel or Google Sheet?

user_56326290
Related