آموزش نحوه محاسبه تاریخ در اکسل

زمانی که در گزارشات و کارهای روزانه خود در اکسل می خواهیم از تاریخ استفاده نماییم ممکن است با سوالات و چالش هایی از قبیل ، چگونه اختلاف بین دو تاریخ را محاسبه نمایم؟ چگونه تاریخ شمسی را به میلادی تبدیل کنم؟ چگونه تاریخ را به حروف بنویسم ؟ چگونه تاریخ روز را در اکسل وارد نمایم وکلی سوال دیگر روربرو شوید .
در این مقاله می خواهیم صفر تا صد تمام نکات محاسبه تاریخ در اکسل را همراه با مثال کاربردی برای شما عزیزان شرح دهیم .

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

در مرحله اول می بایست با نحوه ورود تاریخ شمسی در اکسل آشنا شوید .
تاریخ شمسی در بسیاری از کشورها بخصوص در ایران و افغانستان به عنوان تقویم رسمی کشور، به کار می‌رود.
فرض کنید می خواهیم تاریخ 15 آذر ماه سال 1401 را در اکسل بصورت عدد وارد نماییم . حالت استاندارد نوشتن تاریخ شمسی در اکسل به این صورت می باشد که شما ابتدا 4 رقم سال را وارد کرده یعنی 1401 سپس از جدا کننده اسلش / استفاده کرده و دو رقم ماه که می شود 09 را نوشته و مجدد از جدا کننده استفاده کرده و دو رقم روز که د راین مثال 15 می باشد را وارد می نماییم .

نکته 1 : اگر سال را بصورت دو رقم وارد نمایید ممکن است در مراحل بعدی که می خواهید بر روی آن تغیراتی اعمال نمایید دچار مشکل شوید پس پیشنهاد من به شما این است آن را 4 رقمه وارد نمایید .

نکته 2 : ممکن است شما تاریخ خود را بدون جدا کننده یا همان اسلش وارد نمایید و بعد با تنظیمات فرمت سل آن را درست کرده اما بهتر است تاریخ را با جداکننده اسلش داخل اکسل ثبت نمایید ( فقط در تابع های MIN – MAX MAXIF – MINIF ) تاریخ را بدون جدا کننده و بصورت عددی وارد کنید .

حذف اسلش از تاریخ شمسی :

همانطور که در بالا به آن اشاره شد زمانی که می خواهید از توابع مینیمم و ماکسیمم استفاده نمایید می بایست تاریخ را بصورت عددی و بدون اسلش ثبت کنید .
اما اگر با اسلش تاریخ خود را در اکسل ثبت کردید و می خواهید اسلش تاریخ خود را حذف نمایید کافی است از تابع SUBTITUTE استفاده کنید .

SUBSTITUTE ( text , old_text , new_text)

text – محدوده یا سلول و یا داده ای که باید بررسی شود( که در اینجا تاریخ مد نظر را انتخاب می کنیم )

old_text – متنی که باید پیدا شده و جایگزین شود ( می بایست اسلش را که یک رشته متنی است داخل “/” دبل کوتیشن بگذاریم .

new_text – متن یا داده ای که باید به جای آرگومان دوم قرار بگیرد( در این مرحله از ما می خواهد با چه چیزی جایگزین کنم ؟ که ما فقط ” ” می گذاریم و بینش هیچ چیزی قرار نمی دهیم . دوتا کوتیشن کنار هم به معنای تهی و خالی می باشد ، یعنی اسلش را هیچی جایگزین کن و یا کلا حذفش کن و پرانتز را می بندیم .

=SUBSTITUTE(E3,”/”,””)

سوال اول : شاید برای شما این سوال ایجاد شود از کجا بفهمیم این نوشته ساختار عددی دارد یا خیر ؟

پاسخ : زمانی که متن شما از بعد وارد کردن فرمول و اینتر زدن به سمت چپ تراز شود و یا زمانی که شما فرمول را انتخاب کرده و کلید F9 را فشار دهید به شما ساختار “تاریخ” را نمایش دهد که اگر داخل دبل کوتیشن باشد یعنی خروجی فرمول شما متنی بوده در غیر این صورت عددی می باشد .

سوال دوم :اگر ساختار محتوا من بصورت متنی بود چگونه خروجی فرمول را به یک رشته عددی تبدیل نمایم ؟

پاسخ : برای این سوال دو راه حل وجود دارد

راه حل اول : ساده ترین راه در اکسل مقدماتی آن است که یک عملیات ریاضی ساده و بی اثر بر روی فرمول انجام دهیم یعنی فقط کافی است در ادامه فرمول 0+ ( بعلاوه صفر ) و یا 1* ( ضربدر یک ) را بنویسیم .

SUBSTITUTE(E3,”/”,””)+0=

راه حل دوم : راه فنی برای پاسخ به این سوال این است که شما قبل از فرمول خود از دو عدد منفی – – استفاده نمایید .

SUBSTITUTE(E3,”/”,””)–=

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

اگر تاریخی که در اکسل شما ثبت شده است بدون اسلش می باشد و شما می خواهید به آن تاریخ اسلش اضافه نمایید کافی است از تابع TEXT استفاده نمایید .

TEXT ( value , format_text )

Value : در این قسمت می بایست سلول مد نظر را انتخاب نماییم .

format_text: فرمت عددی مد نظر را باید وارد کنیم که در اینجا ابتدا کوتیشن سپس جایگاه سال که چهار رقمه می باشد با 0000 چهارتا صفر وارد کرده سپس از بک اسلش استفاده کرده ( چون اسلش ساختار متنی می باشد یا باید آن را داخل دبل کوتیشین بگذارید یا از بک اسلش استفاده کنید ) اسلش را نوشته و دوتا 00 که جایگاه ماه هست را وارد می کنیم و مجدد از بک اسلش اسلش و در نهایت دوتا صفر00 برای جایگاه روز و پرانتز را بسته و اینتر را می زنیم و مشاهده می کنید که اسلش به تاریخ ما اضافه شده است . TEXT(D3,”0000\/00\/00″)=

نکته : اگر می خواهید فقط ظاهری سلول شما که تاریخ هست اسلش دار شود کافی است از فرمت سل تب CUSTOM استفاده نمایید .

اختلاف دو تاریخ شمسی

ممکن است برای شما این اتفاق افتاده باشد که بخواهید اختلاف دوتاریخ را حساب نمایید اما نمیدانید چگونه ؟

در بحث حسابداری محاسبه اختلاف دو تاریخ شمسی یکی از واجبات می باشد . برای مثال اختلاف تارخ چک ها را می توان با این روش به راحتی محاسبه کرد .

اگر که از افزونه پروفسور اکسل استفاده می نماید به راحتی در عرض چند ثانیه می توانید با کمک فرمول J_TODAY اختلاف دو تاریخ را محاسبه نمایید .

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

با توجه به مثال که در تصویر زیر مشاهده می نمایید می خواهیم اختلاف تارخ چک ها را با تاریخ امروز بدست آوریم

مرحله اول : نمایش تاریخ روز در اکسل

اگر می خواهید تاریخ روز را در اکسل به نمایش بگذارید کافی است کنترل ; را گرفته وتاریخ روز را مشاهده کنید . که تاریخ بصورت میلادی به ما نمایش داده می شود .

یک شیت جدید باز کرده و با روش بالا تاریخ روز را ثبت می کنیم و چون م یخواهیم تاریخ بصورت سری ادادمه پیدا کند کافی است از HOME تب FILL گزینه SERIES را انتخاب کرده و مطابق شکل عمل نمایید .

تیک گزینه COLUMNS را زده و در قسمت STOPVALUE تاریخی که آخرین تاریخ می باشد به میلادی وارد کرده و در قسمت STEP VALUE می گوییم یک روز یک روز حرکت کن .

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

برای تبدیل تاریخ میلادی به شمسی در اکسل باید تب فرمت سل را باز کرده و گزینه DATE و بعد قسمت LOCAL و CALENDER TYPE باید هر دو را در حالت PERSIAN انتخاب نماییم .

همانطور که مشاهده کنید متوجه می شوید بصورت ظاهری تاریخ ما شمسی شده است اما ما می خواهیم باطن سلول من نیز شمسی شود برای اینکار کافی است فرمت سل را باز کرده و در قسمت CUSTEM قسمت اول براکت که کد فارسی سازی هست ( [$-fa-IR,16] ) را کپی و در فرمول TEXT استفاده نماییم . مطابق با فرمول زیر

TEXT(B2,”[$-fa-IR,16]YYY/MM/DD”)

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

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

اول جایگاه تاریخ روز را مشخص می کنیم برای همین مراحل زیر را انجام دهید .

MATCH($C$5,Sheet2!C2:C367,0)=

تاریخ روز که با اف 4 ثابت می کنیم

شیتی که در آن تاریخ ها را ایجاد کرده ایم قسمت تاریخ شمسی را کامل انتخاب کرده

و بعد عدد صفررا تایپ می کنیم که یعنی دقیقا همین عبارت را جستجو نماید .

برای تاریخ چک ها نیز دقیقا به همین روش انجام می دهیم فقط عدد را دیگر ثابت نمی کنیم و جایگاه تاریخ چک ها را مشخص می نماییم .

و در نهایت اختلاف دو روز را گرفته و اختلاف تاریخ بدست خواهد آمد .

شما با چه چالش و سوالاتی در محاسبه تاریخ در اکسل روبرو می شوید ؟

سوالات خود را با ما در میان بگذارید .