[SOLVED] Convert date locale in google sheet from Gregorian calendar to Jalali calendar

Issue

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

Solution

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)))

---

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)))

Answered By – player0

Answer Checked By – Candace Johnson (BugsFixing Volunteer)