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

user_19863011 0
Viewed 139

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.

script google-sheets excel-formula excel function
2 Answers

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

enter image description here

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

enter image description here

REVERSE:

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

user_56326290

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

enter image description here

enter image description here

user_28729220
Related