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

Table of Contents

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

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

Answered By – player0

Answer Checked By – Candace Johnson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.