استفاده از تاریخ شمسی در اکسل از نیازهای اساسی کاربران این نرم افزار است و تا کنون که این مقاله را منتشر می کنم متاسفانه این نرم افزار پشتیبانی کاملی تاریخ هجری خورشیدی نداشته است و به همین دلیل لازم است که کاربران ایرانی متناست با نیاز خود یکی از روشهای موحود را انتخاب کرده و از آن استفاده کنند.
فهرست محتوا
روشهای استفاده از تاریخ شمسی در اکسل
به طور کلی برای این منظور در حال حاظر دو روش وجود دارد :
- استفاده از تنظیمات داخلی اکسل
- اضافه کردن امکانات به اکسل از طرق ماژول و یا افزونه
هر کدام از این روشها معایب و مزایای خود را دارد که می توانید متناسب با نیاز خود از یکی از روشها استفاده نمائید .
روش اول تنظیمات داخلی اکسل
در این روش با استفاده از تنظیمات خود اکسل می توانید خاصیت تاریخ شمسی را در سلولهای مورد نظر داشته باشید و کافیست طبق توضیحات زیر عمل کنید .
- سلول یا محدوده ای که میخواهید در آن تاریخ به صورت شمسی نمایش داده شود را انتخاب کنید .
- با زدن کلیدهای Ctrl + 1 به Format Cells بروید.
- از تب NUMBER گزینه Date را انتخاب نمائید.
- در تب Date گزینه Local Location را روی Persian(Iran) تنظیم کنید.
- گزینه Calendar Type را روی Persian تنظیم کنید.
- تیک گزینه Input Date According to Selected Calendar را هم بزنید.
با انجام این کارها که مراحل آن را در تصویر هم میبینید ، حالت نمایشی تاریخ وارد شده به صورت شمسی خواهد بود و می توانید از حالتهای مختلف نمایشی ، شکل دلخواه خود را انتخاب کنید .
نکته مهم :
- این تنظیمات در آفیس 2016 به بالا وجود دارد
- وقتی در Format Cells بتوان این تنظیمات را انجام داد پس می توان از تابع Text هم به شکل زیر بهره برد
=TEXT(F3,"[$-fa-IR,96]dddd, d mmmm yyyy")
روش دوم ، ماژول یا افزونه تاریخ شمسی
در این روش که روی تمام ورژنهای آفیس جواب می دهد ، امکانات مورد نیاز در حوزه تاریخ شمسی در اکسل را کاربر به اکسل اضافه می کند .
می دانیم که اکسل توسط VBA قابل گسترش می باشد و می توان امکانات مختلفی را از این طریق به آن اضافه کرد و از آنجا که متاسفانه تا کنون ماکروسافت پشتیبانی کاملی از تاریخ شمسی نکرده است ، برنامه نویسان ایرانی تصمیم گرفتند که با استفاده از قابلیتهای کدنویسی این امکان را به اکسل اضافه کنند که به افزونه تاریخ شمسی و سا ماژول شمسی معروف هستند.
در این مورد هم کاملترین ماژول تاریخ شمسی توسط آفیس باز توسعه داده شده که در ادامه قابل دانلود است .
افزودن ماژولهای شمسی به اکسل
برای اضافه کردن ماژول خیلی راحت مراحل زیر را انجام دهید .
- فایل فشرده تاریخ شمسی در اکسل را دانلود کنید
- آن را از فشردگی خارج کنید
- وارد محیط کد نویسی فایل اکسل شوید ( Alt + F11 )
- در قسمت ماژولها کلیک راست کرده و گزینه Import را انتخاب کنید.
- در پنجره باز شده به مسیری که فایلها را از فشردگی خارج کرده اید و در آنجا قرار دارد بروید .
- یک فایل با پسوند bas وجود دارد آن را انتخاب کنید .
- به محیط اکسل باز گردید و فایل را با پسوند xlsm ذخیذه کنید .
کار تمام شده و این فایل شما حاوی امکانات اضافه شده تاریخ شمسی است .
تاریخ شمسی با استفاده از افزونه
استفاده از افزونه بر خلاف روش قبلی این مزیت را دارد که تمام فایلهای اکسل موجود در کامپیوتر جاری دارای امکانات تاریخ شمسی خواهند بود در روش قبلی فقط همان فایلی که ماژول را به آن اضافه کردید شامل امکانات شمسی می شد .
برای این منظور هم کافیست فایل افزونه را از انتهای این مطلب دانلود کنید و مطابق ویدیو از آن استفاده کنید که پس از آن اکسل شما شامل یک تب اضافه در ریبونها خواهد شد به نام Officebaz که تصویر آن را در ادامه می بینید.
پرسش و پاسخ تاریخ شمسی
من آفیس 2016 ندارم و ماژول هم نمیخوام استفاده کنم راه دیگه ای نیست؟
اگر آفیس 2016 به بالا نداریم و از طرفی نمی توانیم افزونه و یا کاژول استفاده کنیم ، راهی وجود دارد که بشود حداقل نیاز در این حوزه را برآورده کرد . یعنی بتوانیم تاریخ شمسی را به میلادی و تاریخ میلادی را به شمسی تبدیل کنیم .
برای این منظور می توانیم از ترکیب توابع استفاده کنیم . ( این راه بیشتر برای مواقع خاص بوده و توصیه نمی شود )
برای تبدیل تاریخ میلادی به شمسی با فرض اینکه نقدار تاریخ در A1 قرار دارد می توانید از ترکیب فرمول زیر استفاده کنید .
=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)))))
اگر هم با همین شرایط نیاز داشتید که تاریخ شمسی را به تاریخ میلادی تبدیل کنید می توانید از فرمول زیر استفاده نمائید .
توجه داشته باشید که فرض بر این است که تاریخ شمسی به صورت متنی و 10 رقمی وارد شده است مانند 1404/02/23
=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
حال اگر مطالب مفهوم تاریخ در اکسل را مشاهده کرده باشید ، فلسفه عدد 466710 را در انتهای فرمول بالا درک خواهید کرد و پیشنهاد میکنم حتما آن مطلب را ببینید.
به طور کلی روش اول را پیشنهاد می کنید یا دوم ؟
پاسخ به این سوال بسیار بستگی به نیاز شما از مقوله تاریخ شمسی در اکسل دارد ، اگر نیاز شما فقط محدود به نمایش تاریخ و محاسبات پایه ای است و آفیس 2016 به بالا دارید و در محیطی هم هستید که بقیه کاربران هم آفیس نسخه همخوان دارند . خب روش اول کار شما را راه می اندازد .
ولی اگر آشنایی با اکسل دارید و مشکلی با راه اندازی افزونه و یا اضافه کردن ماژول ندارید من گزینه دوم را پیشنهاد می کنم و خودم همیشه از این روش استفاده می کنم
چرا مدعی هستید که تنطیمات خود اکسل و امکانات موجود کامل نیست ؟
برای اینکه با انجام تنظیمات مربوط به تاریخ شمسی در اکسل ، در واقع فقط حالت نمایشی آن تغییر می کند و محاسبات بر پایه تاریخ میلادی انجام می شود .
به عنوان مثال تایع EOMONTH که برای نمایش آخرین روز ماه استفاده می شود ، آخرین روز تاریخ میلادی را نمایش می دهد و برای استفاده در پروژه های ما مثلا دفترچه اقساط در اکسل کاربردی نیست .
این فقط یک مثال از این تفاوت بود .
آیا این روش در آفیس موبایل هم جواب می دهد
متاسفانه کدهای VBA توسط آفیس موبایل پشتیبانی نمی شوند ولی روش فرمول نویسی جواب می دهد .
چرا با روش ماژول ، هربار فایل باز می شود تاریخ به روز نمی شود .
برای حل این مسئله نیاز هست که یک خط کد به رویداد باز شدن ورکبوک اضافه کنید که به شکل زیر است :
Private Sub Workbook_Open()
Application.CalculateFullRebuild
End Subچرا با وجود اضافه کردن کدها ، همچنان کار نمیکند
چند دلیل می تواند داشته باشد ولی مهمترین آن ذخیره نکردن با فرمت Xlsm می باشد ، چرا که باید فایلی که به آن ماژول تاریخ شمسی اضافه کرده اید را با پسوند Xlsm ذخیره کنید .
مزایا و معایب هر روش
مزایای روش اول ( بدون افزونه )
در این روش نیازی به فعال کردن ماکرو نیست
حالت نمایش خیلی سریع انجام می شود
مزایای روش دوم ( با افزونه )
این روش روی تمام ورژنها کار می کند
تقریبا تمام نیازها متناسب با زبان فارسی در آن گنجانده شده
حاوی ماژول تبدیل عدد به حروف فارسی است
معایب بدون افزونه
چون محاسبات و نتیحه آن با تاریخ میلادی است و فقط حالت نمایشی آن شمسی است در برخی پروژه ها کاربرد ندارد
حتما باید آفیس 2016 به بالا داشته باشید
معایب زوش دوم
باید فایل را ماکرو اینیبل ( با پسوند XLSM ) ذخیره کنید و روی موبایل هم کار نخواهد کرد .
اگر از افزونه استفاده کنید هم آن افزونه حتما در سیستم دیگر باید وجود داشته باشد
پیشنهاد برای آپدیت و تکمیل
همیشه وقتی برنامه ای نوشته میشه و یا افزونه ای ارائه میشه وقتی اشکالاتش معلوم میشه که در مقیاس گسترده استفاده بشه و اصولا به همین دلیل هم هست که کمپانی های بزرگ مثل ماکروسافت اول ورژنهایی که تازه خودشون کلی دیباگ کرده و آزمون خطا کردن رو آزمایشی در اختیار جامعه ای از کاربران قرار میدن که ایراداتش معلوم بشه 🙂
تازه بعدش وقتی میاد و عموم مردم استفاده میکنن یه سری ایراد دیگه در میاد ، این ماژول هم همین حالت رو داره من قطعا نمیتونم تمام شرایط رو تست کنم ، کاربر عزیزی تو کامنتها گغته بود که تو تابع AddMonth وقتی به یک سال کبیسه 24 ماه اضافه میشه نتیجه درست نیست که تو آپدیت 2.5.3 این مشکل رو برطرف کردم و ممنون از دوست خوبمون.
به همین دلیل از شما عزیزان همراه تقاضا دارم اگر مشکلی در ماژول مشاهده کردید و یا پیشنهادی برای بهتر شدنش داشتید حتما تو نظرات عنوان کنید.
