تاریخ شمسی در اکسل: درج خودکار و تبدیل میلادی به شمسی
تاریخ شمسی در اکسل: درج خودکار و تبدیل میلادی به شمسی
تصور کنید: یک دفتردار هستید و باید گزارش حضور و غیاب ماهانه ۵۰ کارمند را تهیه کنید. هر روز باید تاریخ را وارد کنید، اما اکسل به‌طور پیش‌فرض تاریخ میلادی نشان می‌دهد. یا حسابدار یک شرکت هستید و می‌خواهید ستون تاریخ فاکتورها را به شمسی تبدیل کنید تا برای مدیرعامل قابل فهم باشد. شاید هم دانشجو هستید و پروژه‌ای دارید که نیاز به نمودار روند زمانی بر اساس تاریخ شمسی دارد. در همه این موقعیت‌ها، کار با تاریخ شمسی در اکسل یک نیاز اساسی است. اما متأسفانه اکسل به‌طور پیش‌فرض از تقویم شمسی پشتیبانی کامل نمی‌کند و کاربران ایرانی باید با روش‌های مختلف این کمبود را جبران کنند.
خبر خوب این است که اکسل قابلیت‌های پنهان و قدرتمندی برای کار با تاریخ شمسی دارد—از تنظیمات ساده فرمت سلول تا افزونه‌های VBA و Power Query. در این راهنما، ۷ روش مختلف را از سطح مبتدی تا پیشرفته یاد می‌گیرید و یاد می‌گیرید کدام روش برای چه سناریویی مناسب‌تر است. فرقی نمی‌کند دانشجو باشید، کارمند، دفتردار یا خانم خانه‌دار—این آموزش برای شماست.
۱. چرا تاریخ شمسی در اکسل یک ضرورت است؟
تقویم شمسی (هجری خورشیدی) تقویم رسمی ایران است و در تمام مکاتبات اداری، گزارش‌های مالی، فاکتورها و اسناد رسمی از آن استفاده می‌شود. اما اکسل به‌طور پیش‌فرض با تقویم میلادی کار می‌کند و کاربران ایرانی با چالش‌های زیر روبه‌رو هستند:
-
نمایش: تاریخ‌ها به‌صورت میلادی نمایش داده می‌شوند و برای مخاطب ایرانی قابل فهم نیستند.
-
محاسبات: محاسبه فاصله بین دو تاریخ شمسی، سن، یا مهلت پروژه با تقویم میلادی دشوار است.
-
گزارش‌گیری: نمودارهای زمانی بر اساس ماه‌های میلادی ساخته می‌شوند، نه ماه‌های شمسی (فروردین تا اسفند).
به همین دلیل، تسلط بر روش‌های کار با تاریخ شمسی در اکسل یک مهارت ضروری برای هر کاربر ایرانی است.
۲. روش اول: تنظیم فرمت سلول (ساده‌ترین روش)
ساده‌ترین و سریع‌ترین روش برای نمایش تاریخ میلادی به‌صورت شمسی، تغییر فرمت سلول است. این روش تاریخ را به شمسی تبدیل نمی‌کند، بلکه همان تاریخ میلادی را با نمایش شمسی نشان می‌دهد.
مراحل:
-
سلول یا محدوده حاوی تاریخ میلادی را انتخاب کنید.
-
راست‌کلیک کنید و Format Cells را انتخاب کنید (یا Ctrl + 1 را بزنید).
-
به تب Number بروید.
-
از قسمت Category، گزینه Date را انتخاب کنید.
-
در لیست Locale (location)، گزینه Persian را پیدا کنید.
-
در لیست Type، فرمت مورد نظر خود را انتخاب کنید (مثلاً
۱۳۹۸/۰۱/۰۱یا۱۳۹۸ فروردین ۰۱). -
OK کنید.
نتیجه: تاریخ میلادی در سلول به‌صورت شمسی نمایش داده می‌شود. اما نکته مهم این است که مقدار واقعی سلول همچنان میلادی است. اگر فرمول‌نویسی کنید، اکسل با تاریخ میلادی محاسبات را انجام می‌دهد.
کاربرد اصلی: برای گزارش‌های نمایشی و اسنادی که فقط نیاز به نمایش دارند، نه محاسبه با تاریخ شمسی.
۳. روش دوم: فرمول [$-fa-IR] و قالب‌بندی سفارشی
اگر می‌خواهید تاریخ را با فرمت دقیق‌تری نمایش دهید یا قالب‌بندی خاصی داشته باشید، از کد فرمت [$-fa-IR] استفاده کنید.
مراحل:
-
سلول حاوی تاریخ میلادی را انتخاب کنید.
-
Ctrl + 1 را بزنید تا پنجره Format Cells باز شود.
-
به تب Number بروید و Custom را انتخاب کنید.
-
در کادر Type، کد فرمت زیر را وارد کنید:
[$-fa-IR,16]yyyy/mm/dd
-
OK کنید.
توضیح کد فرمت:
-
[$-fa-IR]: تعیین زبان فارسی (fa-IR) برای نمایش -
,16: کد تقویم شمسی -
yyyy/mm/dd: فرمت نمایش سال/ماه/روز
نکته مهم: این کد در برخی نسخه‌های اکسل (به‌ویژه نسخه‌های قدیمی‌تر یا نسخه‌های Mac) ممکن است کار نکند. در صورت بروز مشکل، از روش تنظیم Locale (روش اول) استفاده کنید.
نمایش اعداد فارسی: اگر می‌خواهید اعداد به‌صورت فارسی (۱۲۳ به‌جای ۱۲۳) نمایش داده شوند، از کد زیر استفاده کنید:
[$-fa-IR,16]yyyy/mm/dd;@
۴. روش سوم: درج خودکار تاریخ با TODAY و Fill Handle
فرض کنید می‌خواهید یک دنباله از تاریخ‌های شمسی ایجاد کنید—مثلاً تاریخ تمام روزهای یک ماه را برای ثبت حضور و غیاب. اکسل این کار را با تابع TODAY() و قابلیت Fill Handle انجام می‌دهد.
مراحل:
-
در سلول A1، فرمول
=TODAY()را وارد کنید. این فرمول تاریخ امروز را (به میلادی) نمایش می‌دهد. -
فرمت سلول را طبق روش اول یا دوم به شمسی تغییر دهید.
-
سلول A1 را انتخاب کنید. یک دستگیره کوچک در گوشه پایین‌راست سلول ظاهر می‌شود (Fill Handle).
-
دستگیره را به پایین بکشید. اکسل دنباله‌ای از تاریخ‌های متوالی (هر روز یک ردیف) ایجاد می‌کند.
نکته: تابع TODAY() هر روز که فایل را باز کنید، به‌روز می‌شود. اگر می‌خواهید تاریخ ثابت بماند، از Ctrl + ; استفاده کنید (تاریخ امروز را به‌صورت ثابت وارد می‌کند) و سپس فرمت را به شمسی تغییر دهید.
۵. روش چهارم: افزونه‌های VBA فارسی (Persian Calendar)
برای کاربران حرفه‌ای که نیاز به تبدیل واقعی تاریخ میلادی به شمسی و انجام محاسبات با تاریخ شمسی دارند، افزونه‌های VBA بهترین گزینه هستند. این افزونه‌ها توابع سفارشی مانند PERSIAN_TO_GREGORIAN و GREGORIAN_TO_PERSIAN را به اکسل اضافه می‌کنند.
مراحل نصب افزونه behify/persian-calendar-excel:
-
فایل افزونه را از GitHub دانلود کنید.
-
فایل را باز کنید و Alt + F11 را بزنید تا ویرایشگر VBA باز شود.
-
ماژول حاوی کد را کپی کرده و در فایل اکسل خود وارد کنید.
-
فایل را با فرمت
.xlsm(Macro-Enabled Workbook) ذخیره کنید.
توابع کلیدی این افزونه:
| تابع | عملکرد | مثال |
|---|---|---|
=GREGORIAN_TO_PERSIAN(date) |
تبدیل میلادی به شمسی | =GREGORIAN_TO_PERSIAN(A1) |
=PERSIAN_TO_GREGORIAN("1403/05/08") |
تبدیل شمسی به میلادی | =PERSIAN_TO_GREGORIAN("1403/05/08") |
=PERSIAN_DATE(1403, 5, 8) |
ساخت تاریخ شمسی از اعداد | =PERSIAN_DATE(1403, 5, 8) |
=ADD_DAYS_TO_PERSIAN("1403/05/08", 10) |
افزودن روز به تاریخ شمسی | =ADD_DAYS_TO_PERSIAN(A1, 10) |
مزیت اصلی: این توابع تبدیل را به‌صورت واقعی انجام می‌دهند (نه فقط نمایش) و از سال‌های کبیسه نیز پشتیبانی می‌کنند. برخلاف فرمول‌های دستی که در سال‌های کبیسه دچار خطا می‌شوند، افزونه‌های VBA دقیق عمل می‌کنند.
هشدار: افزونه‌های VBA فقط در نسخه دسکتاپ اکسل کار می‌کنند و در Excel for Web یا Google Sheets قابل استفاده نیستند.
۶. روش پنجم: تبدیل با Power Query و تنظیم Locale
Power Query قدرتمندترین ابزار اکسل برای تبدیل ستون‌های حجیم تاریخ است. برخلاف فرمول‌های معمولی که باید برای هر سلول جداگانه نوشته شوند، Power Query یک‌بار تنظیم می‌شود و برای هزاران ردیف اعمال می‌گردد.
مراحل:
-
داده‌های خود را در یک جدول اکسل (Table) قرار دهید (Ctrl + T).
-
به تب Data بروید و From Table/Range را انتخاب کنید.
-
ویرایشگر Power Query باز می‌شود.
-
روی ستون تاریخ راست‌کلیک کنید و Change Type > Using Locale را انتخاب کنید.
-
در پنجره بازشده:
-
Data Type: Date را انتخاب کنید.
-
Locale: Persian را انتخاب کنید.
-
-
OK کنید.
-
روی Close & Load کلیک کنید تا داده‌های تبدیل‌شده به اکسل برگردند.
مزیت: Power Query تمام ستون را یکجا تبدیل می‌کند و برای داده‌های حجیم (هزاران ردیف) بسیار سریع‌تر از فرمول‌های معمولی عمل می‌کند. همچنین این روش قابل تکرار است—هر بار داده جدید اضافه شود، فقط Refresh کنید.
نکته مهم: ممکن است Power Query تقویم شمسی را به‌طور کامل پشتیبانی نکند، اما با تنظیم Locale روی Persian، نمایش تاریخ به شمسی انجام می‌شود.
۷. روش ششم: تبدیل تاریخ شمسی به میلادی (نیاز معکوس)
گاهی نیاز دارید تاریخ شمسی را به میلادی تبدیل کنید—مثلاً برای ارسال به سیستم‌های خارجی یا محاسبات با تقویم میلادی.
روش الف: فرمول دستی (با احتیاط)
یک فرمول تقریبی برای تبدیل شمسی به میلادی وجود دارد:
=DATE(سال_شمسی+621, ماه_شمسی+3, روز_شمسی+21)
هشدار: این فرمول در سال‌های کبیسه و ماه‌های ابتدایی سال دچار خطا می‌شود. برای استفاده دقیق، از افزونه‌های VBA یا Power Query استفاده کنید.
روش ب: استفاده از Power Query
-
ستون تاریخ شمسی را در Power Query بارگذاری کنید.
-
روی ستون راست‌کلیک کنید و Change Type > Using Locale را بزنید.
-
Data Type را Date و Locale را Persian انتخاب کنید.
-
Power Query تاریخ را به میلادی تبدیل می‌کند و می‌توانید با فرمول‌های تاریخ میلادی محاسبات انجام دهید.
۸. جدول مقایسه جامع ۷ روش
| روش | سرعت | دقت | نیاز به افزونه | مناسب برای | سطح |
|---|---|---|---|---|---|
| تنظیم فرمت سلول | ⭐⭐⭐⭐⭐ | ⭐⭐⭐ | خیر | نمایش سریع | مبتدی |
کد [$-fa-IR] |
⭐⭐⭐⭐ | ⭐⭐⭐⭐ | خیر | نمایش با فرمت خاص | مبتدی |
| TODAY + Fill Handle | ⭐⭐⭐⭐ | ⭐⭐⭐⭐ | خیر | تولید دنباله تاریخ | مبتدی |
| VBA (Persian Calendar) | ⭐⭐⭐ | ⭐⭐⭐⭐⭐ | بله | تبدیل واقعی + محاسبات | حرفه‌ای |
| Power Query | ⭐⭐⭐⭐⭐ | ⭐⭐⭐⭐⭐ | خیر | داده‌های حجیم | حرفه‌ای |
| فرمول دستی | ⭐⭐⭐ | ⭐⭐ | خیر | تبدیل تقریبی | متوسط |
| Google Sheets | ⭐⭐⭐⭐ | ⭐⭐⭐ | خیر | کاربران گوگل | مبتدی |
۹. عیب‌یابی: چرا فرمت شمسی کار نمی‌کند؟
| مشکل | علت احتمالی | راه‌حل |
|---|---|---|
| فرمت شمسی در لیست Locale نیست | نسخه اکسل قدیمی یا نصب‌نبودن زبان فارسی | از ویندوز Settings > Time & Language > Language، فارسی را اضافه کنید |
| اعداد به‌صورت لاتین نمایش داده می‌شوند | فونت پشتیبانی نمی‌کند | فونت را به Tahoma یا Arial تغییر دهید |
فرمول [$-fa-IR] خطا می‌دهد |
نسخه اکسل پشتیبانی نمی‌کند | از روش تنظیم Locale (روش اول) استفاده کنید |
| Fill Handle تاریخ را کپی می‌کند، نه ادامه نمی‌دهد | تنظیمات AutoFill | به File > Options > Advanced بروید و Enable fill handle and cell drag-and-drop را فعال کنید |
| تاریخ شمسی پس از ذخیره به میلادی برمی‌گردد | فرمت سلول ذخیره نشده | فرمت را در یک استایل (Style) ذخیره کنید و به همه سلول‌ها اعمال کنید |
| Power Query تاریخ را تشخیص نمی‌دهد | فرمت تاریخ نامعتبر | ابتدا ستون را به Text تبدیل کنید، سپس Using Locale را روی Persian تنظیم کنید |
۱۰. نکات ویژه مشاغل: حسابدار، دفتردار و کارمند
حسابدار / دفتردار:
-
فاکتورها: ستون تاریخ فاکتور را با فرمت
[$-fa-IR,16]yyyy/mm/ddتنظیم کنید تا در چاپ، تاریخ شمسی نمایش داده شود. -
گزارش مالیاتی: برای سامانه مودیان، تاریخ‌ها باید شمسی باشند. از Power Query برای تبدیل ستون تاریخ استفاده کنید.
-
محاسبه سررسید: اگر نیاز به محاسبه سررسید چک‌ها دارید، از توابع VBA افزونه Persian Calendar استفاده کنید.
کارمند اداری / منابع انسانی:
-
حضور و غیاب: با TODAY + Fill Handle دنباله تاریخ شمسی بسازید.
-
محاسبه سن کارکنان: از تابع DATEDIF با تاریخ شمسی استفاده کنید (نیاز به افزونه VBA).
-
گزارش ماهانه: نمودارهای خود را بر اساس ماه‌های شمسی (فروردین تا اسفند) بسازید.
دانشجو:
-
پروژه‌های تحقیقاتی: داده‌های تاریخی را با Power Query به شمسی تبدیل کنید.
-
نمودار روند: پس از تبدیل، PivotTable بسازید و نمودار روند ماهانه شمسی بگیرید.
سوالات متداول (FAQ)
۱. ساده‌ترین روش نمایش تاریخ شمسی در اکسل چیست؟
تنظیم فرمت سلول: راست‌کلیک > Format Cells > Number > Date > Locale > Persian. این روش سریع‌ترین و بدون نیاز به افزونه است.
۲. آیا اکسل به‌طور پیش‌فرض از تقویم شمسی پشتیبانی می‌کند؟
اکسل از تقویم شمسی برای نمایش پشتیبانی می‌کند (از طریق Locale)، اما برای تبدیل واقعی و محاسبات با تاریخ شمسی، نیاز به افزونه VBA یا Power Query دارید.
۳. چرا فرمول‌های دستی تبدیل تاریخ در سال‌های کبیسه خطا می‌دهند؟
فرمول‌های دستی از یک الگوی ساده (سال+۶۲۱، ماه+۳، روز+۲۱) استفاده می‌کنند که سال‌های کبیسه و ماه‌های ابتدایی را در نظر نمی‌گیرد. برای دقت کامل، از افزونه‌های VBA استفاده کنید.
۴. بهترین روش برای تبدیل ستون تاریخ در یک فایل ۱۰,۰۰۰ ردیفی چیست؟
Power Query. یک‌بار تنظیم می‌کنید و برای همه ردیف‌ها اعمال می‌شود. برای داده‌های جدید نیز فقط Refresh کنید.
۵. آیا تاریخ شمسی در Google Sheets هم قابل استفاده است؟
بله، Google Sheets از تنظیمات Locale فارسی پشتیبانی می‌کند. مسیر: File > Spreadsheet settings > Locale > Iran.
۶. چگونه تاریخ شمسی را در نمودارهای اکسل استفاده کنم؟
ابتدا تاریخ را با Power Query به شمسی تبدیل کنید، سپس PivotTable بسازید و نمودار بر اساس ماه‌های شمسی بگیرید.
۷. آیا می‌توانم تاریخ شمسی را در Excel for Web تنظیم کنم؟
Excel for Web از تنظیمات Locale پشتیبانی می‌کند، اما برای افزونه‌های VBA باید از نسخه دسکتاپ استفاده کنید.
جمع‌بندی و مسیر یادگیری حرفه‌ای
شما اکنون با ۷ روش مختلف برای کار با تاریخ شمسی در اکسل آشنا شده‌اید—از تنظیم ساده فرمت سلول تا تبدیل واقعی با VBA و Power Query. با تسلط بر این روش‌ها، می‌توانید گزارش‌های اداری، فاکتورها و اسناد مالی خود را حرفه‌ای‌تر و دقیق‌تر تهیه کنید.
خلاصه مسیر یادگیری:
-
مبتدی: تنظیم فرمت سلول (روش اول) + کد
[$-fa-IR](روش دوم) -
متوسط: TODAY + Fill Handle (روش سوم) + تبدیل شمسی به میلادی (روش ششم)
-
پیشرفته: VBA Persian Calendar (روش چهارم) + Power Query (روش پنجم)
اما تاریخ شمسی فقط یکی از ده‌ها مهارت کاربردی در اکسل است. اگر می‌خواهید مهارت‌های خود را در فرمول‌نویسی، PivotTable، Power Query و داشبوردسازی به سطح حرفه‌ای برسانید، دوره‌های آموزشی تخصصی ما با پروژه‌های واقعی و پشتیبانی دائمی، بهترین مسیر برای شماست. در این دوره‌ها یاد می‌گیرید چگونه داده‌های واقعی کسب‌وکار را تحلیل کنید، گزارش‌های مدیریتی بسازید و به یک تحلیل‌گر داده ارزشمند تبدیل شوید.
همین امروز یک قدم بردارید: یک فایل اکسل باز کنید و فرمت سلول یک تاریخ را به شمسی تغییر دهید. سپس با Power Query یک ستون تاریخ را تبدیل کنید—تفاوت را خودتان خواهید دید.
منابع و مراجع
-
Microsoft Q&A – How to use Persian Dates in Excel
-
GitHub – behify/persian-calendar-excel (VBA Package)
-
Digiato – چگونه در اکسل تاریخ میلادی را به شمسی تبدیل کنیم
-
فرادرس – تاریخ شمسی در اکسل
-
فرادرس – تبدیل تاریخ شمسی به میلادی
-
TechTakumi – تبدیل تاریخ میلادی به شمسی در اکسل
-
Stack Overflow – Power Query Persian Date Conversion
