دو روش مرسوم برای استفاده از تاریخ شمسی در اکسل وجود دارد که در ادامه به آنها خواهیم پرداخت و لازم است بدانید که متاسفانه تا زمان نگارش این مطلب همچنان تاریخ شمسی دتوسط ماکروسافت به طور کامل پشتیبانی نشده که دلایل آن عرض خواهم کرد.
فهرست محتوا
روشهای موجود برای استفاده از تاریخ شمسی در اکسل
البته که روشهای مختلفی تا کنون تست و پیشنهاد شده است که هر کدام ویژگیهای خود را دارد اما در این مطلب بنا به تجربه 20 ساله استفاده از این نرم افزار ، دو روش زیر را بررسی خواهیم کرد تا بهترین نتیجه دست پیدا کنیم.
بدون استفاده از افزونه
در این روش نیازی نیست که توابع اختصاصی به اکسل اضافه کنیم و کافیست که ورژن آفیس ما بالای 2016 بوده و ویندوز 10 یا 11 داشته باشیم . البته این روش مزایا و معایبی دارد که در ادامه به آن خواهیم پراخت.
با استفاده از افزونه
در حال حاضر این روش پیشنهاد می شود ، چرا که در کنار امکانات خود اکسل ، بیش 50 تابع اختصاصی به اکیل اضافه خواهد شد که تقریبا تمام نیاز های شما را در حوزه تاریخ شمسی در اکسل برطرف می کند و محدودیت ورژن اکسل هم نخواهید داشت ، البته این روش هم مزایا و معایب خود را دارد ولی در حال حاضر توسط آفیس باز پیشنهاد می شود .
روش اول تاریخ شمسی بدون افزونه
در این روش با استفاده از تنظیمات خود اکسل اکسل می توانید خاصیت تاریخ را در سلولهای مورد نظر داشته باشید و کافیست طبق توضیحات زیر عمل کنید .
- سلول یا محدوده ای که میخواهید در آن تاریخ به صورت شمسی نمایش داده شود را انتخاب کنید .
- با زدن کلیدهای Ctrl + 1 به Format Cells بروید.
- از تب NUMBER گزینه Date را انتخاب نمائید.
- در تب Date گزینه Local Location را روی Persian(Iran) تنظیم کنید.
- گزینه Calendar Type را روی Persian تنظیم کنید.
- تیک گزینه Input Date According to Selected Calendar را هم بزنید.
با انجام این کارها که مراحل آن را در تصویر هم میبینید ، حالت نمایشی تاریخ وارد شده به صورت شمسی خواهد بود و می توانید از حالتهای مختلف نمایشی ، شکل دلخواه خود را انتخاب کنید .
روش بدون افزونه کجا کاربرد دارد؟
با توجه به عیب بزرگی که استفاده از ورش بدون افزونه برای استفاده از تاریخ شمسی در اکسل وجود دارد ، پس این روش کجا کاربرد دارد ؟
فرض کنید که داده هایی مشابه تصویر زیر دارید که شامل یک ستون از نوع تاریخ می باشد و شما می خواهید معادل تاریخ شمسی آن را ببینید و کار محاسباتی دیگری روی تاریخ ها ندارد . در اینصورت کافیست مراحل زیر را انجام دهید.
البته به این نکته توجه داشته باشید که این فرمول برای آفیس 2016 به بالا و ویندوز 10 یا 11 جواب می دهد .
=TEXT(F3,"[$-fa-IR,96]dddd, d mmmm yyyy")
اگر آفیس 2016 و یا ویندوز 10 و 11 نداشته باشیم نمی توانیم تاریخ میلادی را به شمسی تبدیل کنیم و این کار را بدون افزونه انجام دهیم ؟
چرا راهی وجود دارد که با استفاده از تریکیب توابع بتوانید تاریخ میلادی را به شمسی تبدیل کنید که البته برای داده های زیاد امکان دارد کاهش سرعت به همراه داشته باشد ، به هر حال اگر خواستید می توانید از فرمول زیر استفاده کنید .
=CONCATENATE(INT((A1-7385)/365.25+1299),"/",IF(MOD(IF(INT(MOD((A1-7385)*100,36525)/100)<186,INT(INT(MOD((A1-7385)*100,36525)/100)/31),IF(MOD(INT((A1-7385)/365.25),4)=0,INT((INT(MOD((A1-7385)*100,36525)/100)-186)/30)+6,IF(INT(MOD((A1-7385)*100,36525)/100)<336,INT((INT(MOD((A1-7385)*100,36525)/100)-186)/30)+6,INT((INT(MOD((A1-7385)*100,36525)/100)-336)/29)+11))),12)+1<10,CONCATENATE(0,MOD(IF(INT(MOD((A1-7385)*100,36525)/100)<186,INT(INT(MOD((A1-7385)*100,36525)/100)/31),IF(MOD(INT((A1-7385)/365.25),4)=0,INT((INT(MOD((A1-7385)*100,36525)/100)-186)/30)+6,IF(INT(MOD((A1-7385)*100,36525)/100)<336,INT((INT(MOD((A1-7385)*100,36525)/100)-186)/30)+6,INT((INT(MOD((A1-7385)*100,36525)/100)-336)/29)+11))),12)+1),MOD(IF(INT(MOD((A1-7385)*100,36525)/100)<186,INT(INT(MOD((A1-7385)*100,36525)/100)/31),IF(MOD(INT((A1-7385)/365.25),4)=0,INT((INT(MOD((A1-7385)*100,36525)/100)-186)/30)+6,IF(INT(MOD((A1-7385)*100,36525)/100)<336,INT((INT(MOD((A1-7385)*100,36525)/100)-186)/30)+6,INT((INT(MOD((A1-7385)*100,36525)/100)-336)/29)+11))),12)+1),"/",IF(IF(INT(MOD((A1-7385)*100,36525)/100)<186,MOD(INT(MOD((A1-7385)*100,36525)/100),31)+1,IF(MOD(INT((A1-7385)/365.25),4)=0,MOD(INT(MOD((A1-7385)*100,36525)/100)-186,30)+1,IF(INT(MOD((A1-7385)*100,36525)/100)<336,MOD(INT(MOD((A1-7385)*100,36525)/100)-186,30)+1,MOD(INT(MOD((A1-7385)*100,36525)/100)-336,29)+1)))<10,CONCATENATE(0,IF(INT(MOD((A1-7385)*100,36525)/100)<186,MOD(INT(MOD((A1-7385)*100,36525)/100),31)+1,IF(MOD(INT((A1-7385)/365.25),4)=0,MOD(INT(MOD((A1-7385)*100,36525)/100)-186,30)+1,IF(INT(MOD((A1-7385)*100,36525)/100)<336,MOD(INT(MOD((A1-7385)*100,36525)/100)-186,30)+1,MOD(INT(MOD((A1-7385)*100,36525)/100)-336,29)+1)))),IF(INT(MOD((A1-7385)*100,36525)/100)<186,MOD(INT(MOD((A1-7385)*100,36525)/100),31)+1,IF(MOD(INT((A1-7385)/365.25),4)=0,MOD(INT(MOD((A1-7385)*100,36525)/100)-186,30)+1,IF(INT(MOD((A1-7385)*100,36525)/100)<336,MOD(INT(MOD((A1-7385)*100,36525)/100)-186,30)+1,MOD(INT(MOD((A1-7385)*100,36525)/100)-336,29)+1)))))
بدیهی است که در فرمول بالا به جای A1 باید آدرس مد نظر خود را قرار دهید.
البته برای تبدیل تاریخ شمسی به تاریخ میلادی هم بدون افزونه می توانید از ترکیب توابع زیر استفاده کنید و به یاد داشته باشد که تاریخ شمسی که مورد استفاده قرار میدهید باید 10 کاراکتری باشد یعنی به همراه اسلش نوشته شده باشد به عنوان مثال تاریخ 1403/02/28 تا تبدیل به شکل صحیح انجام شود .
=IF(MOD(VALUE(LEFT(A1,4)),4)=0,(VALUE(LEFT(A1,4))-1)*365+(IF((VALUE(MID(A1,6,2))-1)<7,(VALUE(MID(A1,6,2))-1)*31,IF((VALUE(MID(A1,6,2))-1)>6,(VALUE(MID(A1,6,2))-1)*30+6)))+VALUE(RIGHT(A1,2))+INT((VALUE(LEFT(A1,4))-1)/4)+1,(VALUE(LEFT(A1,4))-1)*365+(IF((VALUE(MID(A1,6,2))-1)<7,(VALUE(MID(A1,6,2))-1)*31,IF((VALUE(MID(A1,6,2))-1)>6,(VALUE(MID(A1,6,2))-1)*30+6)))+VALUE(RIGHT(A1,2))+INT((VALUE(LEFT(A1,4))-1)/4))-466710
مزایای تاریخ شمسی بدون افزونه
در این روش نیازی به فعال کردن ماکرو نیست
حالت نمایش خیلی سریع انجام می شود
معایب تاریخ شمسی بدون افزونه
در این حالت محاسبات صحیح انجام نمی شود و از توابع تاریخی خود اکسل هم نمی توانید به درستی استفاده کنید به عنوان مثال تابع EOMONTH در اکسل آخرین روز یک تاریخ را نمایش می دهد و وقتی از این تابع برای تاریخ شمسی استفاده می کنید ، نتیجه صحیح نمایش داده نمی شود .
در واقع این روش فقط برای تغییر حالت نمایش تاریخ به صورت شمسی است.
در این روش حتما باید آفیس 2016 به بالا داشته باشید و ویندوز شما هم 10 یا 11 باشد.
روش دوم تاریخ شمسی در اکسل با افزونه
این مراحل را به ترتیب انجام دهید تا امکانات تاریخ شمسی به درستی به فایل اکسل شما اضافه شود و بتوانید از آنها استفاده نمائید.
زمان مورد نیاز: 5 دقیقه
دانلود فایل مورد نیاز و افزودن به فایل اکسل
در انتهای مطلب فایل فشرده را دانلود کنید و از فشردگی خارج نمائید .
فایل اکسل خود را باز کنید ( سعی کنید در آن واحد فقط یک فایل باز باشد )
با زدن کلیدهای Alt + F11 وارد قسمت کد نویسی شوید .
فایل modOfficebazShamsi.bas را بکشید و در قسمت ماژولها مانند تصویر رها کنید.
قسمت کد نویسی را ببندید.
فایل خود ذخیره کنید و پسوند xlsm را حتما انتخاب نمائید.
توابع مهم تاریخ شمسی
بعد از انجام مراحل بالا ، فایل شما حاوی توابعی شده است که به شما کمک می کند تا از امکانات تاریخ بهره مند شوید که مهمترین آنها را در تصویر فوق با مثال مشاهده می کنید .
نکته مهم این است که با این ماژول شما امکانات تبدیل عدد به حروف در اکسل را هم دارید و همانطور که در تصویر می بینید می توانید از آن استفاده کنید .
کار تمام است و فایل شما حاوی امکانات تاریخ شمسی در اکسل می باشد و در ادامه مثالهایی را انجام می دهیم تا بیشتر با آنها آشنا شوید.
تولید:
- آفیس باز
ابزارها:
- فایل ماژول تاریخ شمسی آفیس باز (دانلود در انتهای مطلب )
مزایای تاریخ شمسی با افزونه
متناسب با نیازهای فارسی زبانان بهینه سازی شده
تقریبا تمام نیازهای مرتبط با تاریخ شمسی را برآورده میکند
بیش از 50 تابع کاربردی دارد به راحتی می توان پروژه هایی مانند اقساط در اکسل را با آن ایجاد کرد
شامل توابع تبدیل عدد به حروف در اکسل است
معایب تاریخ شمسی با افزونه
باید فایل را ماکرو اینیبل ( با پسوند XLSM ) ذخیره کنید و روی موبایل هم کار نخواهد کرد .
مثالها و آموزشهای تاریخ شمسی در اکسل
به دلیل اینکه تاریخ در اکسل مبحث بسیار گسترده ای است و مثالهای متنوعی در این خصوص می توان ارائه د اد ، لینکهای مفید در این خصوص را در این قسمت قرار می دهیم که شماهده و انجام هر کدام می تواند شما را در درک بهتر از این موضوع یاری دهد .
- به دست آوردن اختلاف بین دو تاریخ شمسی در اکسل
- تبدیل تاریخ میلادی به شمسی و برعکس | با افزونه و بدون افزونه
- لیست فروش ماهانه ترکیب با تابع FILTER
- آموزش ساخت تقویم شمسی در اکسل ( داینامیک )
- اضافه کردن ماه به تاریخ شمسی
- نکات پیشرفته تاریخ شمسی | افزودن فرم و آپدیت خودکار و…
- لیست ماههای شمسی و روزهای هفته
- بررسی صحت تاریخ شمسی وارد شده
- جمع بین دو تاریخ شمسی در اکسل
- محاسبه سابقه کار افراد در اکسل
- روزهای کاری و تعطیل بین دو تاریخ شمسی
- یادآوری سررسید چک و تاریخ تولد
در ادامه مثالهای متنوعی را از تاریخ شمسی در اکسل با هم انجام می دهیم و اگر شما مشکل یا مثال دیگری مد نظر دارید حتما در قسمت پرسش و پاسخ همین مطلب عنوان کنید تا این آموزش را آپدیت نگهداریم.
پیشنهاد برای آپدیت و تکمیل
همیشه وقتی برنامه ای نوشته میشه و یا افزونه ای ارائه میشه وقتی اشکالاتش معلوم میشه که در مقیاس گسترده استفاده بشه و اصولا به همین دلیل هم هست که کمپانی های بزرگ مثل ماکروسافت اول ورژنهایی که تازه خودشون کلی دیباگ کرده و آزمون خطا کردن رو آزمایشی در اختیار جامعه ای از کاربران قرار میدن که ایراداتش معلوم بشه 🙂
تازه بعدش وقتی میاد و عموم مردم استفاده میکنن یه سری ایراد دیگه در میاد ، این ماژول هم همین حالت رو داره من قطعا نمیتونم تمام شرایط رو تست کنم ، کاربر عزیزی تو کامنتها گغته بود که تو تابع AddMonth وقتی به یک سال کبیسه 24 ماه اضافه میشه نتیجه درست نیست که تو آپدیت 2.5.3 این مشکل رو برطرف کردم و ممنون از دوست خوبمون.
به همین دلیل از شما عزیزان همراه تقاضا دارم اگر مشکلی در ماژول مشاهده کردید و یا پیشنهادی برای بهتر شدنش داشتید حتما تو نظرات عنوان کنید.
پرسشهای پرتکرار
چرا ماژول را اضافه کرده ام ولی کار نمیکند ؟
دلایل مختلفی می تواند داشته باشد ولی معمول ترین آن این دو مشکل است که باید برطرف شود .
1- قبل از باز کردن فابل روی آن راست کلیک کنید و به Properties بروید و تیک Unblock اگر وجود داشت بزنید . ( این حالت بیشتر زمانی رخ می دهد که فیال خود را به سیستم دیگر منتقل میکنید )
2- فایل را باید ماکرو اینیبل کنید
چرا با هر بار اجرای اکسل تاریخ بروز نمیشود ؟
برای اینکه تاریخهای استفاده شده در فایل اکسل شما همزمان با باز کردن آن آپدیت شوند باید یک خط کد به فایل خود اضافه کنید که بسیار ساده است و در مطلب نکات پیشرفته تاریخ شمسی توضیح داده شده است.
آیا امکان استفاده از این افزونه در موبایل وجود دارد؟
متاسفانه خیر هر فایل اکسلی که ماکرو اینیبل شود و ماژولهای کاربر به آن اضافه شوند قابلیت اجرا در موبایل را از دست می دهند .
پیشنهاد شما کدام روش است؟
پاسخ به این سوال بستگی به سطح نیاز شما و ورژن اکسل که استفاده می کنید دارد ، ولی تجربه بنده در این خصوص ثابت کرده که در حال حاضر استفاده از افزونه تاریخ شمسی بهترین انتخاب است و تقریبا تمام نیازها را برآورده میکند.
