Function Problems

How to Use a Persian Calendar with the Excel YEAR Function

Maira Mehtab Sep 28, 2026 869 views

Question details

Extract or display a Persian (Jalali) calendar year from a standard date in Excel.

How to Use a Persian Calendar with the Excel YEAR Function

Product: Microsoft Excel
Device & OS: not provided
Scenario: Users need to calculate, manage, or report on dates using the Persian calendar system, but the native date functions do not return the correct regional year.
Observed behavior: The standard Excel YEAR function only returns years based on the Gregorian calendar and cannot directly calculate or output Persian calendar years.

Before you start

Ensure your date values are stored as valid Excel serial numbers rather than plain text before attempting to convert them to a Persian calendar format.

Solution 1Recommended

Use a Localized TEXT Formula to Extract the Persian Year

Use the TEXT function combined with a specific regional locale code to convert a Gregorian date into a Persian year number.

Excel's built-in YEAR function is hardcoded to the Gregorian calendar. To extract the Persian year, you must bypass the YEAR function and instead use the TEXT function with the Persian locale code [$-fa-IR,16].

Select the target cell

Click on the empty cell where you want the extracted Persian year to appear.

Enter the TEXT formula

Type the formula =--TEXT(A2, "[$-fa-IR,16]YYYY"), replacing 'A2' with the cell reference that contains your date.

Apply the calculation

Press Enter. The double negative (--) in the formula automatically converts the extracted text string back into a numeric value.

Use a Localized TEXT Formula to Extract the Persian Year

Understanding the Locale Code: The formatting code '[$-fa-IR,16]' instructs Excel to process and output the date using Farsi (Iran) regional settings and calendar rules.

Solution 2

Apply Custom Cell Formatting for Persian Dates

Change how the date is displayed visually in the spreadsheet without altering the underlying Gregorian serial number.

Seamless Spreadsheet Solution

Extract and Format Persian Dates Easily with WPS Office

WPS Spreadsheet provides robust support for regional date formatting and localized TEXT formulas, allowing you to seamlessly manage Persian calendar data without complex workarounds.

1. Open your document: Launch WPS Spreadsheet and open the file containing your date records.
2. Apply the localized formula: Select an empty cell and input the formula =--TEXT(A2, "[$-fa-IR,16]YYYY").
3. Calculate the result: Press Enter to instantly view the calculated Persian year.
4. Format visually: Alternatively, use the 'Format Cells' menu (Ctrl+1) to apply custom date formats like [$-fa-IR,16]dddd globally across your sheet.

Fully compatible with Microsoft Excel formulas including TEXT and TODAYSupports advanced custom number formatting and international locale codesFree to use for personal and professional data managementLightweight software with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does the YEAR function give me the wrong year for Persian dates?

The standard =YEAR() function in Excel is built exclusively for the Gregorian calendar system. It cannot interpret or output Persian (Jalali) years directly, which is why a localized TEXT formula must be used to perform the conversion.

How do I get the current Persian year today using a formula?

You can combine the TODAY function with the localized TEXT function. Enter =--TEXT(TODAY(), "[$-fa-IR,16]YYYY") into an empty cell to return the current Persian year numerically.

Does the Persian calendar formatting work in older versions of Excel?

The [$-fa-IR,16] locale code requires Excel 2013 or newer, alongside adequate Windows language packs, to render the Persian calendar correctly. If you see standard Gregorian years instead, your system may lack support for this specific regional tag.

Excel & Data Problems