Shamsi dates in Excel and Sheets (and the formula that gets 29% wrong)

You have a column of Persian dates and you need real Gregorian ones you can sort, filter and subtract. Two things go wrong, and the second is much worse than the first, because it does not look like anything went wrong at all.

First: the format is a costume, not a conversion

Microsoft documents this plainly. From the YEAR function reference:

“Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value.”

So a cell showing 1403/01/15 may be storing serial number 45385 and still answer 2024 to =YEAR(A1). Users hit this in the wild — one Microsoft community thread reports exactly it: “for the Hijri date it returns the Gregorian values even though the date is converted using the cell format.”

Formatting is a display layer. If you need the Gregorian date as data, you have to compute it.

Second: the formula everyone copies is wrong, just not where you’d look

This formula circulates widely and was the accepted answer on Microsoft Q&A question 1188161 (asked 9 March 2023, answered the next day):

=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, (m-1)*30+6),
  total,       (y-1)*365 + full_months + d + INT((y-1)/4),
  IF(MOD(y,4)=0, total+1, total) - 466710)

The same arithmetic was already circulating in a December 2017 Microsoft forum thread, pasted by someone asking for help rather than supplied by Microsoft.

Here is why it survived review. It is exactly right for Jalali 1380–1399 — Gregorian 2001 to 2021. Every date anyone would naturally spot-check falls in that window. Test it on today, on last year, on a recent invoice, and it agrees every time.

Step outside those two decades and it drifts:

The half that matters now, not in 2029

Most write-ups of this bug lead with the future. That buries the real problem.

Across Jalali 1300–1450, the failures split 10,950 a day early and 5,121 a day late. The early ones are the older years — and older years are where birth dates live. Iranian passports, birth certificates, old contracts and personnel records are full of 13xx dates from the 1340s to 1370s.

• 1 Farvardin 1371 is 21 March 1992. The formula says 20 March 1992.
• 15 Khordad 1351 is 5 June 1972. The formula says 4 June.

An HR clerk converting a 1351 birth date with that formula gets the wrong day today, and nothing in the spreadsheet flags it. The 1408 breakage — the year the drift flips direction, in 2029 — is real but secondary.

Why it drifts: Microsoft’s Excel answer contradicts Microsoft’s own docs

The formula’s leap rule is MOD(y,4)=0. The real rule is not four-yearly. Microsoft’s own .NET PersianCalendar documentation states it:

“A leap year is a year that, when divided by 33, has a remainder of 1, 5, 9, 13, 17, 22, 26, or 30.”

Eight leap years in 33, not eight in 32. Look at the set and you can see the trap:

Between 1403 and 1408 there is a five-year gap. Every formula built on “every 4th year” must eventually slip a day there, and the -466710 constant only papers over it while the running leap count happens to be off by exactly 10 — which is true for 1380–1399 and not for long.

A pure formula that is exact

No Apps Script, no VBA, no Power Query, no array constants. Paste into one cell and format the result as a date:

=LET(t,     SUBSTITUTE(TRIM(A1),"/","-"),
   y,     VALUE(LEFT(t,4)),
   m,     VALUE(MID(t,6,2)),
   d,     VALUE(RIGHT(t,2)),
   p,     y-1,
   r,     MOD(p,33),
   leaps, 8*INT(p/33) + IF(r<18, INT((r+3)/4), INT((r+2)/4)),
   doy,   IF(m<8, (m-1)*31, (m-1)*30+6) + d,
   365*p + leaps + doy - 466700)

Each binding does one job:

Checked against a calendar engine over Jalali 1178–1501 (Gregorian 1799–2123): 0 wrong out of 118,338 dates. Restricted to what Excel can represent — 1 March 1900 onward — it is 0 wrong out of 81,449.

Stated limits, because any arithmetic Persian formula has them:

• Use it for Jalali 1279–1501. Outside that, arithmetic implementations begin to disagree with each other, so no single formula is authoritative.
• It targets the 1900 date system, the Windows default. For a legacy 1904-system workbook use -468162 instead, from Microsoft’s documented 1,462-day difference between the two systems. That variant is derived, not separately swept.
• Excel’s 1900 system counts a 29 February 1900 that never existed, so dates in January and February 1900 are off by one in Excel itself. Irrelevant in practice, worth knowing.

It works the same in Google Sheets, which supports LET.

A trap worth more than the formula

That 2017 thread shows a failure mode no formula fixes. A user pasted Shamsi dates like 1396/04/03 from an Iranian billing site, and the copy dropped the leading digits to 96/04/03. Excel happily read that as a Gregorian date in 1996 and stored serial 35128 — 4 March 1996, about 21 years from the intended 24 June 2017.

The cell then displayed under a Persian-looking number format, so it looked converted. Before trusting any conversion, widen the column and check that your source text still has four digits in the year.

For one-off dates, skip the spreadsheet: use the converter directly. The leap rule behind all of this is worked through in Persian leap years explained, and the year-boundary problem in Shamsi to Gregorian conversion.