ثبت نام دوره آنلاین VBA به زبان ساده
سبد خرید
0

سبد خرید شما خالی است.

جستجو
Generic filters

مفهوم تاریخ در اکسل

تاریخ در اکسل
۵/۵ - (۱۴ امتیاز)

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

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

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

اولین و مهم ترین چیزی که راجع به تاریخ میلادی باید بدونید اینه که تاریخ در واقع عدد هست. به شکل ۱ نگاه کنید. اگر تاریخ ها رو در حالت General/Number قرار بدیم به عدد تبدیل میشن. اما مفهوم این عدد چیست؟

تاریخ در اکسل - مفهوم مقدار تاریخ

شکل ۱- مفهوم مقدار تاریخ

همونطور که در شکل ۱ می بینید، هر تاریخ برابر با یک عدد است. مبنای محاسبات تاریخ در اکسل، ۱/۱/۱۹۰۰ است و عدد ۴۳۰۰۱ یعنی این تعداد روز از تاریخ ۱/۱/۱۹۰۰ گذشته است. برای همین تاریخ ۱/۱/۱۹۰۰ معادل عدد ۱ هست.

Persian-Calendar-Icon

افزونه تقویم شمسی در اکسل
افزونه بسیار کاربردی و هوشمند جهت درج و تبدیل تقویم شمسی

مشاهده افزونه تقویم شمسی در اکسل

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

تاریخ در اکسل - محاسبه اختلاف دو تاریخ

شکل ۲- محاسبه اختلاف دو تاریخ

نکته:
اگر تاریخ بزرگتر رو از کوچکتر کم کنیم، مسلما عددی منفی خواهد بود. اما این عدد منفی در برخی سیستم ها بصورت ###### دیده میشه.  برای حل این مشکل و مشاهده اختلاف بصورت عدد منفی، کافیه از مسیر زیر، تیک use 1904 date system رو بزنیم.

Excel Options/Advance/ When calculating this workbook

 

خب با درک منطق و مفهوم تاریخ میلادی در اکسل، حالا میتونید محاسبات متنوعی رو انجام بدید. در ادامه چند تابع کاربردی در خصوص تاریخ معرفی میکنم که در محاسبات مفید خواهد بود:

تاریخ تابع خروجی  شرح
 ۹/۲۳/۲۰۱۷  YEAR(B5)  ۲۰۱۴  این تابع سال را فراخوانی میکنه
 ۹/۲۳/۲۰۱۷  MONTH(B6)  ۹  این تابع ماه را فراخوانی میکنه
 ۹/۲۳/۲۰۱۷  DAY(B7)  ۲۳  این تابع روز را فراخوانی میکنه
 ۲۳ (روز) ۹ (ماه) ۲۰۱۷ (سال)  DATE(D8,C8,B8)  ۹/۲۳/۲۰۱۷  این تابع تاریخ حاصل از سال، ماه و روز رو نمایش میده

یکی از توابع خیلی کاربردی مخصوصا برای دوستانی که با افراد خارج از کشور کار میکنند، تابع شماره هفته است. همونطور که میدونید، یکسال از ۵۲ هفته تشکیل شده است. برای اینکه ببینیم هر تاریخ در چندمین هفته سال هست از تابع weeknum استفاده میکنیم. به مثال زیر توجه کنید:

 تابع شماره هفته تاریخ
 WEEKNUM(L5)  ۵۲  ۱۲/۲۶/۲۰۱۷
 WEEKNUM(L6)  ۱  ۱/۵/۲۰۱۸

یکی دیگه از توابع کاربردی تاریخ در اکسل  تابع Today هست. این تابع تاریخ جاری سیستم رو نشون میده. آرگومان هم نداره. یعنی با تایپ =Today() در یک سلول، با باز کردن فایل در هر روز، تاریخ همان روز رو مشاهده میکنید.

مشابه همین تابع Now() هم وجود داره که زمان جاری سیستم رو برمیگردونه. بعبارتی تاریخ و ساعت رو. برای درک بهتر تفاوت این دو تابع به شکل ۳ توجه کنید:

تفاوت تابع Now() و Today()

شکل ۳- تفاوت تابع Now() و Today()

همونطور که در شکل ۳ می بینید، معادل عددی خروجی تابع Today()، ۴۳۰۰۱ هست ولی معادل عددی خروجی تابع NOW() 43001.66118 هست. در واقع عدد ۰.۶۶۱۱۸ معادل ساعت ۱۵:۵۲ هست.

نکته:
کلید میانبر برای ثبت تاریخ سیستم در اکسل کلید Ctrl+: و ثبت ساعت سیستم در  اکسل Ctrl+Shift+: است.
توجه داشته باشید که این تاریخ ثابت هست و بروز نمیشه.

 

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

همچنین مطالب زیر بهتون پیشنهاد میشه:

محاسبات در یک بازه تاریخی

مرتب سازی انواع تاریخ در اکسل

 

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • سالار ۱۷ آبان ۱۳۹۹ / ۹:۱۰ ب٫ظ

    سلام
    سوال داشتم
    در یک فایل در چند ستون اطلاعاتی وجودد ارد در یک ستون تاریخ وجود دارد در یک ستون شماره بیمه میخواهم با تابع نویسی بیمه شدگان مراجعه کننده در یک ماه را با tru fals مشخص کنیم لطف راهنمای بفرمایید
    قبلا من از تابع =(right(lefty2;4);2 برای پیدا کردن یک یا ئو جز مشترک در یک سلول استفاده کرده ام
    میخواهم ۱۳۹۶/۰۶/۲۷
    ۱۳۹۶/۰۶/۲۵
    ۱۳۹۶/۰۷/۵
    ۱۳۹۶/۰۷/۱۰ را در ستونی بیاورم که فقط عد ماه ۰۶ یا ۰۷ را نشان بدهد چه تابعی بنویسم؟ سپاس گذارم

    • حسنا خاکزاد ۱۸ آبان ۱۳۹۹ / ۱:۰۸ ب٫ظ

      درود
      فرمولی که نوشتید درسته
      از mid هم میتونید استفاده کنید

  • pascal ۱ آبان ۱۳۹۹ / ۱۰:۰۱ ب٫ظ

    برای جستجو یا پیدا کردن داده های بین دوتاریخ در جدول بشرطی که تاریخ ها بردیف نباشند (مثل تعداداستخدام پرسنل در ۵ سال گذشته یک اداره

    • حسنا خاکزاد ۷ آبان ۱۳۹۹ / ۲:۱۲ ب٫ظ

      درود
      سوالتون خیلی واضح نیست
      به نظر میرسه که با Countifs به نتیجه میرسید

  • ارزو خراد ۹ اسفند ۱۳۹۸ / ۱۰:۲۳ ب٫ظ

    سلام وقت بخیر .من یه لیستی از تاریخ انقضای کالاها دارم که فرمت date دارند و بایدآنها را وارد یه لیستی بکنم که فرمت text داره(امکان تغییر فرمت شیت مقصد وجود نداره ) چطور تاریخ ها رو کپی کنم که تبدیل به عددنشن و به همون صورت تاریخ نمایش داده شوند.ممنون

    • حسنا خاکزاد ۳۰ فروردین ۱۳۹۹ / ۱۲:۴۰ ب٫ظ

      درود
      از تابع text استفاده کنید ببینید جواب نیازتون رو میده؟

  • elham ۲ دی ۱۳۹۸ / ۳:۳۶ ب٫ظ

    سلام وقت بخیر
    برای محاسبه اختلاف دو ساعت و تاریخ به طور همزمان در حالیکه فرمت ساعت به ساعت و دقیقه هست و تاریخ به روز و ماه و سال و کمتر و بیشتر از ۲۴ ساعت هست از فرمول زیر استفاده نموده ام به جواب نمی رسم ممکنه راهنمایی بفرمایید.
    =(J_diff(D2,F2)-1)*24+(24-A2)+B2
    تاریخ مبدا ساعت مبدا تاریخ مقصد ساعت مقصد
    ۱۳۹۷/۷/۲ ۱۶:۵۷ ۱۳۹۷/۷/۲ ۲۱:۴۱
    ۱۳۹۷/۱۰/۱۲ ۲۰:۱۱ ۱۳۹۷/۱۰/۱۳ ۱۲:۴۴
    ۱۳۹۷/۸/۲ ۰۸:۴۸ ۱۳۹۷/۸/۲ ۱۵:۱۸
    ۱۳۹۷/۸/۲۸ ۰۱:۱۷ ۱۳۹۷/۸/۲۸ ۰۳:۱۹
    ۱۳۹۷/۸/۲۸ ۰۳:۵۴ ۱۳۹۷/۸/۲۸ ۰۴:۲۷
    ۱۳۹۷/۱۱/۱۹ ۱۸:۲۱ ۱۳۹۷/۱۱/۲۰ ۰۳:۲۸

  • سارا ۲۷ دی ۱۳۹۷ / ۴:۲۱ ب٫ظ

    چطور میشه زمانی که مشغول به کار هستم به شکل اتومات در یک سلول درج بشه. تا اینجایی که دانش اکسلی من به ذهنم خطور میده فقط میدونم که میتونم یه کنترل شیفت و دو نقطه موقع شروع کارم بزنم و یکی هم موقع اتمام و بعد اونها رو توی سلول مورد نظر از هم کم کنم. ولی خب یه راه حرفه ای میخوام که مثل نرم افزارهای مدیریت زمان هر وقت که مشغول کار میشم تایمر رو روشن و هر وقت میرم استراحت یا استوپ میکنم،تایمر هم استوپ بشه..

    • حسنا خاکزاد ۲ بهمن ۱۳۹۷ / ۲:۳۵ ب٫ظ

      درود بر شما
      باید کد نویسی کنید و با VBA این مسئله رو طراحی کنید

  • سارا ۲۷ دی ۱۳۹۷ / ۴:۱۷ ب٫ظ

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

    • حسنا خاکزاد ۲۷ دی ۱۳۹۷ / ۹:۴۹ ب٫ظ

      درود بر شما
      میانبرها، فقط برای ثبت داده هستن. تابع یا ابزار نیستن که در منوها موجود باشه

      • Sara ۲۸ دی ۱۳۹۷ / ۱۰:۴۸ ق٫ظ

        چه جالب. خیلی ممنون. آخه من خودم خیلی دنبال این گشته بودم که چطوری باید زمان رو به شکل غیرمتغییر ثبت کنم ولی پیدا نکرده بودم تا اینکه از شما یادگرفتم و این سوال رو واسه این پرسیدم که بفهمم چرا خودم نتونستم جواب سوالمو پیدا کنم! چه جوری میتونستم خودم این میانبر رو پیدا کنم؟ بالاخره باید یه گزینه یا کامندی براش تو اکسل تعبیه شده باشه.

  • ایدین ۲۷ مهر ۱۳۹۶ / ۱۱:۰۹ ب٫ظ

    با تشکر

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان