تاریخ شمسی در اکسل: قالب تقویمی، جدول تقویم و راه‌حل‌های فرمولی

مشکلی که هر کاربر ایرانی دارد

اکسل تاریخ را به‌صورت عدد سریال میلادی نگه می‌دارد و توابعی مثل EOMONTH، NETWORKDAYS و گروه‌بندی Pivot بر اساس ماه میلادی کار می‌کنند. کاربر ایرانی اما گزارش ماهانه‌ی شمسی می‌خواهد. راه درست این است: داده را همیشه به‌صورت تاریخ واقعی (عدد سریال) ذخیره کنید و فقط نمایش یا گروه‌بندی را شمسی کنید. تاریخ شمسی‌ای که به شکل متن «1403/05/12» ذخیره شود، قابل محاسبه نیست.

راه ۱: قالب تقویمی شمسی (Excel 365 و نسخه‌های جدید 2019/2021)

در Format Cells ← Date، زبان (Locale) را Persian (Iran) و Calendar type را هجری شمسی انتخاب کنید. معادل کد سفارشی آن:

قالب سلول:    [$-fa-IR,16]yyyy/mm/dd;@
             [$-fa-IR,16]dddd d mmmm yyyy     → سه‌شنبه ۱۲ مرداد ۱۴۰۳
به‌صورت متن:  =TEXT(A2,"[$-fa-IR,16]yyyy/mm/dd")
سال شمسی:    =--TEXT(A2,"[$-fa-IR,16]yyyy")
ماه شمسی:    =--TEXT(A2,"[$-fa-IR,16]m")
سال-ماه:     =TEXT(A2,"[$-fa-IR,16]yyyy-mm")    → کلید مناسب برای گروه‌بندی ماهانه

مقدار سلول همچنان عدد سریال میلادی است؛ پس جمع و تفریق روزها درست کار می‌کند. محدودیت‌ها: ۱) ورود مستقیم «1403/05/12» معمولاً به تاریخ تبدیل نمی‌شود؛ ۲) در نسخه‌ها یا buildهای قدیمی‌تر این نوع تقویم در فهرست نیست و کد بالا تاریخ میلادی نشان می‌دهد؛ ۳) گروه‌بندی خودکار Pivot همچنان میلادی است، پس باید ستون کمکی «سال-ماه شمسی» بسازید.

راه ۲: جدول تقویم؛ قابل‌اعتمادترین روش تبدیل شمسی به میلادی

برای تبدیل تاریخ‌های شمسی متنی (مثلاً خروجی نرم‌افزار حسابداری) به تاریخ واقعی، یک بار یک جدول دوستونی بسازید و همیشه از آن جست‌وجو کنید. در Excel 365:

=LET(
  d, SEQUENCE(DATE(2040,12,31)-DATE(2000,1,1)+1, 1, DATE(2000,1,1)),
  HSTACK(TEXT(d,"[$-fa-IR,16]yyyy/mm/dd"), d)
)

این فرمول حدود پانزده هزار سطر می‌سازد: ستون اول متن شمسی، ستون دوم تاریخ میلادی. خروجی را Copy و Paste Values کنید، به جدول (Ctrl+T) با نام Cal تبدیل کنید و ستون‌های سال، ماه، نام ماه و فصل شمسی را هم اضافه کنید. حالا:

تبدیل شمسی متنی به تاریخ:  =XLOOKUP(A2, Cal[Shamsi], Cal[Miladi], "تاریخ نامعتبر")
تاریخ‌های بدون صفر (1403/5/9) را اول استاندارد کنید:
=TEXT(TEXTBEFORE(A2,"/"),"0000")&"/"&TEXT(TEXTBEFORE(TEXTAFTER(A2,"/"),"/"),"00")&"/"&TEXT(TEXTAFTER(A2,"/",-1),"00")

در Excel 2016 و 2019 که SEQUENCE و XLOOKUP ندارند، همین جدول را یک بار در نسخه‌ی جدید یا با یک برنامه‌ی کوچک بسازید و به‌صورت مقدار نگه دارید؛ به‌جای XLOOKUP از INDEX/MATCH استفاده کنید (فصل ۴). همین جدول بعداً در Power Query و Pivot ستون «ماه شمسی» را تأمین می‌کند.

نکته‌هایی که کمتر کسی می‌داند

• اگر در تنظیمات Region ویندوز تقویم را شمسی کنید، Ctrl+; تاریخ جاری را شمسی نمایش می‌دهد، اما همین فایل روی سیستم همکارتان میلادی دیده می‌شود. قالب صریح [$-fa-IR,16] مستقل از تنظیمات ویندوز است.
• متن‌های شمسی با قالب ثابت yyyy/mm/dd به‌درستی مرتب می‌شوند (چون صفر پیشوند دارند)؛ «1403/5/9» و «1403/12/1» بدون صفر ترتیب غلط می‌دهند.
• برای اختلاف روز بین دو تاریخ شمسی متنی، هر دو را با جدول تقویم به میلادی ببرید و کم کنید؛ هرگز رشته‌ها را عدد فرض نکنید (اسفند ۲۹ یا ۳۰ روزه است).
• ماه‌های ۱ تا ۶ شمسی ۳۱ روزه‌اند؛ پایان ماه شمسی را از جدول تقویم با MAXIFS(Cal[Miladi],Cal[YM],"1403-05") بگیرید، نه با EOMONTH.
• تاریخ‌های قبل از ۱۹۰۰ میلادی (قبل از ۱۲۷۹ شمسی) در اکسل تاریخ نیستند؛ برای داده‌ی تاریخی آن‌ها را متن نگه دارید.