تبدیل تاریخ میلادی به شمسی در اکسل

•  تبدیل تاریخ میلادی به شمسی در اکسل

کی از مهمترین مسائلی که کاربران اکسل با آن روبرو هستند ، تبدیل تاریخ در اکسل است. در این مطلب از بخش آموزش اکسل پیشرفته قصد دارم تا شما را با سه روش برای استفاده از فرمول تبدیل تاریخ میلادی به شمسی آشنا کنم ، پس تا انتهای این مطلب با ما همراه باشید.

• روش اول : استفاده از قابلیت اکسل فرمت نویسی در Excel ( قابل اجرا از اکسل 2013 به بعد)

برای استفاده از این روش می بایست مطابق تصویر زیر عمل کنید. فرض کنید ستونی دارید که در آن تاریخ های میلادی درج شده است. حالا می خواهید تا در مقابل این ستون تاریخ های شمسی معادل آن را قرار دهید.

برای انجام اینکار روی سلولهایی که در ستون تاریخ شمسی قرار دارند راست کلیک کرده و گزینه Format Cells را انتخاب می کنیم. پنجره Format Cells باز می شود ، حالا باید در تب Number وارد بخش Custom شوید.

حالا در کادر قرمز مشخص شده در تصویر بالا کد fa-ir,16]yyyy/mm/dd-$] را بجای کلمه General وارد می کنیم و دکمه Ok را کلیک میکنیم ؛ نتیجه بصورت زیر خواهد بود.

• روش دوم : استفاده از قابلیت تقویم شمسی + فرمت نویسی در Excel ( قابل اجرا از اکسل 2016 به بعد)

روش دیگری که می توانید از آن استفاده کنید یک روش ترکیبی از قابلیت تقویم شمسی داخلی اکسل همراه با فرمت نویسی جهت اصلاح نمایش ، تاریخ شمسی می باشد. برای انجام اینکار پس از انتخاب سلول یا سلولهایی که قصد داریم ؛ تاریخ شمسی در آنها به نمایش در آید ، راست کلیک کرده و گزینه Format Cells را انتخاب می کنیم.

در پنجره باز شده از تب Number وارد بخش Date می شویم و مانند تصویر زیر از لیست Locale بایستی (Persian(Iran را انتخاب کنیم ، بعد از انجام اینکار یک لیست جدید به زیر لیست قبلی اضافه می شود که Calendar Type نام دارد. در این لیست بایستی Persian را انتخاب کرد.

بعد از انجام اینکار در اکسل سلول حاوی تاریخ میلادی به شمسی تبدیل می شود اما یک مشکلی وجود دارد و آن هم نحوه نمایش تاریخ شمسی است که باید آنرا اصلاح کنیم. به تصاویر زیر توجه کنید:

برای اصلاح نمایش تاریخ شمسی در این روش بایستی مجددأ روی همان سلول راست کلیک انجام دهیم و اینبار بایستی  از تب Number وارد بخش Custom شویم و کد fa-ir,16]yyyy/mm/dd-$] را وارد کنیم ؛ ملاحظه می کنید که نحوه نمایش تاریخ اصلاح می گردد.

• روش سوم : استفاده از کدهای VBA

یکی دیگر از روشهایی که میتوانیم برای تبدیل تاریخ میلادی به شمسی در اکسل از آن استفاده کنیم ؛ استفاده از کدهای آماده VBA است ؛ در درون این کدها توابع آماده قرار دارد که از طریق آن می توان عمل تبدیل را براحتی انجام داد. در ادامه با نحوه استفاده از تابع اختصاصی J_JALALDATE آشنا خواهید شد.

در گام اول بایستی پس از دانلود فایل های ضمیمه آنها را از طریق محیط کدنویسی VBA در داخل فایل خود Import نمائید. (لازم به ذکر است این کدها توسط آقای میدانی تهیه شده و در اختیار عموم قرار گرفته است) برای ایمپورت کردن این فایل ها ابتدا کلیدهای ترکیبی Alt+F11 را بصورت همزمان فشار دهید. سپس فایل های زیر را تک تک Import کنید.

1. 1. Date_Module.bas
   2. DateClass.cls
   3. Persian_Tools.bas

برای استفاده از تابع J_JALALDATE ابتدا به معرفی گرامر این تابع می پردازیم ؛ این تابع دارای دو آرگومان است که در ادامه با آنها آشنا خواهید شد. نکته ای که در هنگام استفاده از آن بایستی به آن توجه داشته باشید Format Cells سلولی است که تاریخ میلادی در داخل آن نوشته شده است که باید از نوع Text باشد.

J_JALALDATE(MDATE;Mode)=

• • MDATE : تاریخ میلادی یا آدرس سلولی است که تاریخ میلادی در درون آن قرار گرفته است ؛ شکل تاریخ باید بصورت روز/ماه/سال باشد.
  • Mode : انتخاب نوع نمایش سال ؛ برای نمایش 4 رقمی سال عدد 1 و برای نمایش 2 رقمی سال عدد 2 را وارد می کنیم.

• تبدیل تاریخ شمسی به میلادی در اکسل

برای تبدیل تاریخ شمسی به میلادی در اکسل هم تابعی بنام J_GregorianDate در فایلی که دانلود نموده اید وجود دارد ؛ این تابع نیز همانند تابع J_JALALDATE دارای دو آرگومان است ؛ که در ادامه با آن آشنا خواهید شد.

J_GregorianDate(MDATE;Mode)=

• MDATE : تاریخ شمسی یا آدرس سلولی است که تاریخ شمسی در درون آن قرار گرفته است ؛ شکل تاریخ باید بصورت روز/ماه/سال باشد.
• Mode : انتخاب نوع نمایش سال ؛ برای نمایش 4 رقمی سال عدد 1 و برای نمایش 2 رقمی سال عدد 2 را وارد می کنیم.

• نمایش تاریخ روز جاری ویندوز در اکسل

برای آنکه بتوانیم تاریخ روز جاری کامپیوتر خود را در نرم افزار اکسل به نمایش درآوریم ؛ بایستی از تابع J_TODAY استفاده کنیم ؛ در هنگام استفاده از این تابع ؛ دقت داشته باشید که تاریخ میلادی سیستم شما صحیح باشد ؛ چون در غیر اینصورت تاریخ شمسی بصورت صحیح به نمایش در نخواهد آمد. این تابع دارای یک آرگومان است که در ادامه به معرفی آن می پردازیم.

J_TODAY(MODE)

• MODE : در صورتی که عدد 1 را قرار دهیم ؛ سال بصورت 4 رقمی و در صورت گذاشتن عدد 2 ؛ سال بصورت دو رقمی به نمایش در خواهد آمد.

• نمایش چندمین روز هفته

برای نمایش چندمین روز هفته و اینکه امروز چند شنبه است ؛ بایستی از تابع J_WEEKDAY استفاده کنیم ؛ این تابع دو آرگومان بصورت زیر است.

J_WEEKDAY(JDATE,MODE )

• JDATE : در این آرگومان تاریخ شمسی یا آدرس سلولی که حاوی یک تاریخ شمسی است را وارد می کنیم. در صورتی که می‌خواهیم بصورت مستقیم تاریخ شمسی را وارد کنیم و از آدرس سلول استفاده نکنیم ؛ بایستی حتمأ تاریخ را بین یک دابل کوتیشن “” وارد کنیم.
• MODE : با گذاشتن عدد 1 ؛ تابع بجای نشان دادن شماره روز هفته ؛ چند شنبه بودن آن روز را نمایش خواهد داد.

• اضافه کردن عدد به تاریخ شمسی

در برخی از موارد لازم است تا عددی را به یک تاریخ شمسی اضافه کنیم و تاریخ جدید بدست آمده را به نمایش درآوریم. فرض کنید میخواهیم 5 روز را به تاریخ 1399/10/16 اضافه کنیم ، برای انجام اینکار بایستی از تابع J_ADDDAY استفاده کنیم. این تابع دارای 2 آرگومان است که بصورت زیر می باشد.

J_ADDDAY(JDATE,NUMBER,MODE )

• JDATE : در این آرگومان تاریخ شمسی یا آدرس سلولی که حاوی یک تاریخ شمسی است را وارد می کنیم. در صورتی که می‌خواهیم بصورت مستقیم تاریخ شمسی را وارد کنیم و از آدرس سلول استفاده نکنیم ؛ بایستی حتمأ تاریخ را بین یک دابل کوتیشن “” وارد کنیم. در این آرگومان می توانیم از تابع J_TODAY استفاده کنیم تا در صورت نیاز بتوانیم تاریخ روز جاری را بجای تاریخ شمسی قرار دهیم.
• NUMBER : عددی که میخواهیم به تاریخ اضافه شود را در این آرگومان قرار می دهیم. در صورتی که عدد گذاشته شده در این آرگومان منفی باشد ؛ بصورت معکوس عمل کرده و باعث کم شدن آن از تاریخ خواهد شد.
• MODE : این آرگومان اختیاری است و در صورتیکه عدد 1 را در آن قرار دهیم سال بصورت 4 رقمی و در اگر عدد 2 را قرار دهیم ؛ سال بشکل 2 رقمی در تاریخ جدید ایجاد شده به نمایش درخواهد آمد.

• محاسبه تفاضل تاریخ شمسی

فرض کنید می خواهید سن کارمندان شرکت را براساس تاریخ تولد آنها محاسبه کنید ؛ برای انجام اینکار نیاز به محاسبه اختلاف بین دو تاریخ شمسی خواهید داشت ؛ نخست تاریخ تولد و دیگری تاریخی روزی که میخواهید تا آن روز سن افراد را محاسبه کنید. بدین منظور می بایست از تابع J_DIFF استفاده کنیم ؛ گرامر این تابع بصورت زیر است.

  J_DIFF(JDATE1,JDATE2)

• JDATE1 : در این آرگومان اولین تاریخ شمسی یا آدرس سلولی که حاوی آن است را وارد می کنیم. در صورتی که می‌خواهیم بصورت مستقیم تاریخ شمسی را وارد کنیم و از آدرس سلول استفاده نکنیم ؛ بایستی حتمأ تاریخ را بین یک دابل کوتیشن “” وارد قرار دهیم. در مثالی که داریم تاریخ تولد را بایستی وارد می کنیم.
• JDATE2 : دومین تاریخ شمسی در این آرگومان قرار خواهد گرفت ؛ و مطابق مثال تاریخ روزی که میخواهیم سن کارمندان تا آن روز محاسبه شود را وارد می کنیم. استفاده از دابل کوتیشن در ورود مستقیم تاریخ الزامی است.

در صورتیکه جای تاریخ تولد که در آرگومان اول و امروز در آرگومان دوم را عوض کنیم ؛ نتیجه بشکل یک عدد منفی نمایش داده خواهد شد. درنهایت با تقسیم کردن عدد بدست آمده از تابع بر عدد 365 به سن کارمندان دست پیدا خواهیم کرد.

• تبدیل تاریخ میلادی به شمسی با تابع J_JALALDATE

یکی از توابعی که می توانیم برای تبدیل تاریخ میلادی به شمسی در اکسل از آن استفاده کنیم تابع J_JALALDATE است ؛ این تابع دارای دو آرگومان بصورت زیر است:

J_JALALDATE (MDATE,Mode)

• MDATE : در این آرگومان تاریخ میلادی یا آدرس سلولی که حاوی آن است را وارد می کنیم. نکته ای که بایستی در هنگام استفاده از آدرس یک سلول حاوی تاریخ میلادی بدانیم ؛ فرمت آن سلول است که حتمأ باید Text باشد. در صورتی که می‌خواهیم بصورت مستقیم تاریخ شمسی را وارد کنیم و از آدرس سلول استفاده نکنیم ؛ بایستی حتمأ تاریخ را بین یک دابل کوتیشن “” وارد قرار دهیم.
• Mode : در صورتی که عدد 1 را قرار دهیم ؛ سال بصورت 4 رقمی و در صورت گذاشتن عدد 2 ؛ سال بصورت دو رقمی به نمایش در خواهد آمد.

• کم کردن عدد از تاریخ شمسی

در صورتیکه بخواهیم عددی را از یک تاریخ شمسی کم کرده و تاریخ جدید ایجاد شده را به نمایش درآوریم ، فرض کنید میخواهیم 5 روز را از تاریخ 1399/10/16 کسر کنیم ، بایستی از تابع J_SUBDAY که بدین منظور ساخته شده است استفاده کنیم ، این تابع دارای سه آرگومان بشکل زیر است.

J_SUBDAY(JDATE,NUMBER,MODE )

• JDATE : در این آرگومان تاریخ شمسی یا آدرس سلولی که حاوی یک تاریخ شمسی است را وارد می کنیم. در صورتی که می‌خواهیم بصورت مستقیم تاریخ شمسی را وارد کنیم و از آدرس سلول استفاده نکنیم ؛ بایستی حتمأ تاریخ را بین یک دابل کوتیشن “” وارد کنیم.
• NUMBER : عددی که میخواهیم از تاریخ کم شود را در این آرگومان قرار می دهیم.
• MODE : این آرگومان اختیاری است و در صورتیکه عدد 1 را در آن قرار دهیم سال بصورت 4 رقمی و در اگر عدد 2 را قرار دهیم ؛ سال بشکل 2 رقمی در تاریخ جدید ایجاد شده به نمایش درخواهد آمد.