11

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

player0
  • 124,011
  • 12
  • 67
  • 124
Majid
  • 421
  • 6
  • 19

2 Answers2

13

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

ale13
  • 5,679
  • 3
  • 10
  • 25
  • Thanks for your solution. It worked. However, It's really weird that I have two different output in different cells !! for instant February 13, 2021 4:15 PM will convert to 1399/11/24 in a cell and in another cell, it will be converted to 1399/11/25 !!! Do you know why ? – Majid Feb 22 '21 at 17:37
  • 1
    Just like mentioned above, please check the appsscript.json manifest file of your project and adjust the timezone accordingly. – ale13 Feb 22 '21 at 17:41
  • My mistake. Thanks. BTW, would you please let me know how to check/set timezone in appsscript.json. Thanks – Majid Feb 22 '21 at 17:45
  • I found it . Thank you very much – Majid Feb 22 '21 at 17:50
  • I think this answer should be marked as the correct answer. The other answer depends on an external php file running, which I don't feel comfortable doing that. – Hamid Mar 11 '22 at 04:19
  • @ale13 By the way, I can't find that `appsscript.json` file, where is it located? – Hamid Mar 11 '22 at 04:21
4

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?

player0
  • 124,011
  • 12
  • 67
  • 124