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

Question

Does anyone know how to convert a Persian date to a Gregorian date using an Excel / Google Sheet function? for example:

1401/06/06

to:

2022/08/28

P.S: Earlier I found a function to convert Gregorian to Jalali written by Amir Fo, But my question is about converting from Persian (Shamsi) to Gregorian.

Moiety

answer updated... – Meurer

Answer 1

Here is a pure Excel solution, which does not require an internet connection or Power Query.

Disclaimer: I've found the function here and made some modifications to make it more readable. BTW, I've tested the function for the period 1396/07/01 to 1401/07/01 and it works OK, but use it with extensive testing and at your own risk.

Basically, the function calculates the total days since 1278/10/11 Shamsi (which is equal to the Excel date origin, 1900-01-01). The result of the function is an integer (date value).

Below, you will find the solution in two formats, one using the new LET function (for Excel 2021 and Office 365) and one without:

Note that the Shamsi date should be in the format 1401/07/21.

Using LET:

=LET(
  y,VALUE(LEFT(A1,4)),
  m,VALUE(MID(A1,6,2)),
  d,VALUE(RIGHT(A1,2)),
  full_months,IF((m-1)<7,(m-1)*31,IF((m-1)>6,(m-1)*30+6)),
  total,(y-1)*365+full_months+d+INT((y-1)/4),
  IF(MOD(y,4)=0,total+1,total)-466710
)

Legacy version:

IF(
  MOD(VALUE(LEFT(A1,4)),4)=0,
  (VALUE(LEFT(A1,4))-1)*365+(
    IF(
      (VALUE(MID(A1,6,2))-1)<7,
      (VALUE(MID(A1,6,2))-1)*31,
      IF(
        (VALUE(MID(A1,6,2))-1)>6,
        (VALUE(MID(A1,6,2))-1)*30+6
      )
    )
  )+VALUE(RIGHT(A1,2))+INT((VALUE(LEFT(A1,4))-1)/4)+1,
  (VALUE(LEFT(A1,4))-1)*365+(
    IF(
      (VALUE(MID(A1,6,2))-1)<7,
      (VALUE(MID(A1,6,2))-1)*31,
      IF(
        (VALUE(MID(A1,6,2))-1)>6,
        (VALUE(MID(A1,6,2))-1)*30+6
      )
    )
  )+VALUE(RIGHT(A1,2))+INT((VALUE(LEFT(A1,4))-1)/4)
)-466710

To convert the resulting integer to date, either change the cell format to Date or use the following formula:

TEXT(A2,"yyyy-mm-dd")

Yahrzeit

Try for example to convert 1412/05/01 from Persian (Jalali) to Gregorian. Via this formula it returns 2033-07-23, while it should be 2033-07-22. Any idea why? – Clementius

@MohsenKarbassi It is probably due to leap years, but I haven't done extensive research to identify the cause of the problem. There are periods where the formula can be off by a day or two, and then there are correct periods. That's why I added the disclaimer at the beginning of my answer. – Yahrzeit

Answer 2

try:

=TEXT(VLOOKUP("Gregorian calendar", IMPORTHTML("https://date-today.com/en/shamsi-"&
 REGEXEXTRACT(TO_TEXT(A1), "\/(\d+)")*1&"-"&
 REGEXEXTRACT(TO_TEXT(A1), "\/(\d+)\/")*1&"-"&
 REGEXEXTRACT(TO_TEXT(A1), "\d{4}")*1&"-to-gregorian-calendar.html", "table", 1), 2, ), 
 "e/mm/dd")

or:

=TEXT(VLOOKUP("Gregorian calendar", IMPORTHTML("https://date-today.com/en/shamsi-"&
 REGEXEXTRACT(TO_TEXT(A1), "\d+")*1&"-"&
 REGEXEXTRACT(TO_TEXT(A1), "\/(\d+)\/")*1&"-"&
 REGEXEXTRACT(TO_TEXT(A1), "\d{4}")*1&"-to-gregorian-calendar.html", "table", 1), 2, ), 
 "yyyy/mm/dd")

---

REVERSE:

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

Meurer

Can you please share an example in google sheets? Currently, this formula is not working for me @Meurer – Moiety

@MahdiOmrani sure: docs.google.com/spreadsheets/d/… – Meurer

To get day of a date like 1402/10/19, use \/(\d+)$ in order to get latest part – Hoedown

Answer 3

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range or from within sheet
• When the PQ Editor opens, over on the right under Applied Steps, the second step will be #"Changed Type"

  • Edit that step to add fa-IR as the culture

      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}} , "fa-IR")
    

    • This is the same as Changed Type with locale using Persian as the locale

Kayleigh

It doesn't work on Google Sheets – Moiety

@MahdiOmrani Of course not. Power Query is a feature of Excel. You asked for a solution in Excel OR Sheets. Note that the previous answer will not work in Excel. – Kayleigh

Don't you have a query that works in Google Sheets also? @Ron – Moiety

@MahdiOmrani if you need to do it as a query in sheets, I suggest you adapt the algorithm shown you in the previous answer to a script that will run in sheets. Then post back or ask a new question, if you are having difficulty with that script. Be sure to tag it appropriately. – Kayleigh

Answer 4

Microsoft Excel supports all calendar conversion. The key is to follow procedures one by one.

suppose We want to convert cell A1=10/10/1401 from Persian calendar to English Calendar and set the result in the Cell B1

Step 1:

Right Click on B1 > Format cells... from Number tab in the category select Date and change it to desire calendar type, in this case English. For B1 also type the formula =A1.

Step 2:

Right click on A1 > Format cells... from Number tab in the category select Date and change the calendar type to whatever your date type is; in this case Persian.

Tick the check mark input dates according to selected calendar

Step 3:

Enter your Persian date in cell A1. You'll get the converting date in cell B1.

Be aware of the order you enter the year and month and the day.

Weig