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

دسته ای از توابع در اکسل که مربوط به تاریخ و زمان میباشند Date & Time Functions نام دارند. از توابع تاریخ و زمان در اکسل میتوان در محاسبات مربوط به کار کردها، Time Sheet ها، جداول زمان بندی، برنامه ریزی ها و یا فایل های اکسل مربوط به مدیریت و اجرای پروژه ها استفاده نمود. با توجه به گستره کاربرد این دسته از توابع یادگیری توابع تاریخ و زمان در اکسل بسیار مهم و کاربردی میباشد. برای آشنایی با اصول فرمول نویسی ها در اکسل توصیه میشود مقاله ی “فرمول نویسی در اکسل” را مطالعه نمایید.

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

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

تاریخ و زمان در اکسل به صورت عدد میباشند، به طور کلی، در اکسل هم تاریخ و هم زمان به صورت اعداد مورد استفاده قرار میگیرند و مانند اعداد میتوان بر روی آنها عملیات ریاضی انجام داد. هر روز در اکسل برابر یک است، یعنی یک روز از صفر شروع میشود و با یک به پایان میرسد، این بدان معناست که عدد صفر در اکسل برابر با ساعت ۰۰:۰۰:۰۰ و عدد ۱ برابر با ساعت ۲۴:۰۰:۰۰ میباشد، لذا در اکسل مثلاً عدد ۰٫۵ برابر با ۱۲:۰۰:۰۰ میباشد و عدد ۰٫۷۵ برابر با ۱۸:۰۰:۰۰ است. به این ترتیب هر ساعتی از روز را که خواستید میتوانید به این طریق محاسبه نمایید، به عنوان مثال ساعت ۰۸:۱۳:۲۱ در اکسل برابر با ۳۶۰۰×۸ بعلاوه ۶۰×۱۳ بعلاوه ۲۱ تقسیم بر ۳۶۰۰×۲۴ میباشد که برابر است با عدد ۰٫۳۴۲۶، این عدد در واقع یک Serial Number است که متناظر با ساعت ذکر شده میباشد.

توضیحات ذکر شده در بالا را در نظر بگیرید، مبدا تاریخ در اکسل January 1, 1900 میباشد، یعنی عدد یک برابر با ساعت ۰۰:۰۰:۰۰ این روز است، با این حساب عدد ۲ برابر با ساعت ۰۰:۰۰:۰۰ دوم ژانویه ۱۹۰۰ (January 2, 1900) میباشد، پس نتیجه میگیریم مثلاً عدد ۱٫۵ برابر با ساعت ۱۲:۰۰:۰۰ روز اول ژانویه ۱۹۰۰ میباشد، و به همین ترتیب مثلاً February 8, 1986 (هشتم فوریه ۱۹۸۶) برابر با عدد ۳۱۴۵۱ است و بنابراین عدد ۳۱۴۵۱٫۲۵ (یا Serial Number = ۳۱۴۵۱٫۲۵) برابر با ساعت ۰۶:۰۰:۰۰ روز هشتم فوریه ۱۹۸۶ است. با این تفاسیر تاریخ و ساعت معادل با اعداد کمتر از ۱ (زود تر از ساعت ۰۰:۰۰:۰۰ اول ژانویه ۱۹۰۰) در اکسل تعریف نشده اند، و از طرفی آخرین روز تاریخ اکسل December 29, 9999 برابر با عدد ۲۹۵۸۴۶۳ میباشد.

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

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

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

همانطور که اشاره شد تاریخ و زمان در اکسل اعداد یا Serial Number هستند، هر عددی بین ۱ تا ۲۹۵۸۴۶۳٫۹۹۹ بیانگر یک لحظه از یک روز میباشد، توابعی که در ادامه ی این مقاله معرفی میشوند آن دسته از توابع هستند که به کمک آنها میتوان فهمید هر Serial Number چه سال، ماه، روز، ساعت، دقیقه و ثانیه ای را در بر دارد، در ادامه با دو تابع کاربردی دیگر برای بدست آوردن زمان و تاریخ فعلی آشنا میشویم.

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

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

تابع YEAR در اکسل

خروجی تابع YEAR سال مربوط به سریال نامبر آرگومان است، به عنوان مثال اگر آرگومان عدد ۳۱۴۵۱٫۳۵۸ باشد (این عدد مربوط به ساعت ۰۸:۳۵:۳۱ روز هشتم فوریه ۱۹۸۶ میباشد)، خروجی تابع ۱۹۸۶ است. لازم به تذکر مجدد است که خروجی این تابع همواره عددی بین ۱۹۰۰ تا ۹۹۹۹ میباشد. سینتکس این تابع به صورت زیر است:

YEAR (serial_number)

تابع MONTH در اکسل

سینتکس این تابع دقیقاً همانند تابع YEAR میباشد و خروجی آن عددی بین ۱ تا ۱۲ معادل با ماه Serial Number آرگومان میباشد، برای عدد بالا خروجی این تابع ۲ است.

تابع DAY در اکسل

سینتکس این تابع هم دقیقاً همانند تابع YEAR میباشد و خروجی آن عددی بین ۱ تا ۳۱ معادل با روز ماه Serial Number آرگومان میباشد، برای عدد بالا خروجی این تابع ۸ است.

تابع WEEKDAY در اکسل

سینتکس این تابع هم دقیقاً همانند تابع YEAR میباشد و خروجی آن عددی بین ۱ تا ۷ معادل با روز هفته Serial Number آرگومان میباشد، برای عدد بالا خروجی این تابع ۷ است. لازم به ذکر است که روز ۱ معادل با یکشنبه، ۲ دوشنبه و الی آخر، از آنجایی که هشتم فوریه ۱۹۸۶ شنبه میباشد خروجی این تابع عدد ۷ است.

تابع HOUR در اکسل

سینتکس این تابع هم دقیقاً همانند تابع YEAR میباشد و خروجی آن عددی بین ۱ تا ۲۳ معادل با ساعت Serial Number آرگومان میباشد، برای عدد بالا خروجی این تابع ۸ است.

تابع MINUTE در اکسل

سینتکس این تابع هم دقیقاً همانند تابع YEAR میباشد و خروجی آن عددی بین ۱ تا ۵۹ معادل با دقیقه Serial Number آرگومان میباشد، برای عدد بالا خروجی این تابع ۳۵ است.

تابع SECOND در اکسل

سینتکس این تابع هم دقیقاً همانند تابع YEAR میباشد و خروجی آن عددی بین ۱ تا ۵۹ معادل با ثانیه Serial Number آرگومان میباشد، برای عدد بالا خروجی این تابع ۳۱ است.

جدول زیر خلاصه ای از توابع بالا را نشان میدهد:

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

دو تابع NOW و TODAY برای بدست آوردن لحظه ی فعلی با توجه به ساعت و تاریخ تنظیم شده بر روی کامپیوتر شما میباشند، هر دو تابع بدون آرگومان هستند و سینتکس مشابه دارند.

تابع NOW در اکسل

این تابع زمان و ساعت فعلی کامپیوتر را در فرمت تاریخ و زمان نشان میدهد، Syntax تابع NOW به صورت زیر است:

NOW ()

تابع TODAY در اکسل

این تابع تاریخ فعلی (تاریخ امروز به روایت تقویم کامپیوتر شما) را نشان میدهد و تنها تفاوت آن با تابع NOW اینست که این تابع ساعت را نشان نمیدهد و به نمایش تاریخ امروز بسنده میکند.

TODAY ()

اشتراک گذاری در شبکه های اجتماعی: share on facebook share on google plus share on linkedin

۴۳ نظر

  1. حامد

    با عرض خسته نباشید و تشکر بخاطر آموزش های خوب شما
    فقط یه سوال
    فرمول تبدیل تاریخ میلادی به شمسی چطوریه؟
    من تاریخ آنلاین سیستم رو به برنامه میدم اما نمیشه به شمسی تبدیلش کرد

    1. حامد قدیمی

      سلام، خواهش میکنم، تابعی برای تبدیل تاریخ شمسی و میلادی به یکدیگر در اکسل وجود ندارد، شما باید از توابع موجود در غالب Add-ins ها استفاده نمایید.

  2. امید

    سلام راهنمایی بفرمائید ۰۸:۵۹:۰۰ در صورتی که بافرمول(INT(X6/$B$25000= درجدول برمبانی ۰۷:۳۰ معادل یک روز تبدیل میکنه همین ساعت رو بر مبانی ۸ ساعت محاسبه کنه
    عدد ۸*۳۶۰۰ معادل ۲۸۸۰۰ رو جایگزین کردم جواب نداد
    ممنون

    1. حامد قدیمی

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

  3. سعید

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

    1. حامد قدیمی

      سلام، باید از توابع تاریخ، شرطی و ابزار Conditional Formatting استفاده کنید. مقالات مربوطه را بخوانید.

  4. امیر

    سلام خسته نباشید ”
    خدمتون عارضم که بنده در یک اکسل چند pivot درست کردم و می خواهم در شیت آخر از یکجا فقط تاریخ وارد کنم و در pivot های دیگر بر اساس آن فیلتر شود؟؟؟؟
    ممنون می شوم اگر راهنماییم کنید”

    1. حامد قدیمی

      سلام، این کار ممکن نیست، مگر با برنامه نویسی و یا اینکه مرجع تاریخ تمام نمودارها یکسان بوده و به محل مورد نظر متصل باشد.

  5. نصب آسانسور

    سلام
    یه سوال داشتم اگه لطف کنید و جواب بدین ممنون میشم
    آیا امکانش هست که sumifs رو در محدوده match انجام داد مثلا شما از بین کل روزهای سال که در اکسل ثبت کردین میخواین یکبار از اول فروردین تا آخر فروردین گزارش گیری کنین یکبار دیگه میخواین از مثلا ۲۰ فروردین تا ۸ اردیبهشت گزارش بگیرین
    ممنون میشم کمکم کنید

  6. آرش کریمی

    سلام ، چطور میتونم یه سلول رو قبل از اینکه توش تاریخ رو وارد کنم آماده سازی کنم ،‌مثلا سال توش خورده باشه و نشه اون رو تغییر داد مثل –/–/۱۳۹۶

    1. حامد قدیمی

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

    1. حامد قدیمی

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

  7. پرنیان

    سلام . بسیار عالی بود مقاله ،
    من راستش یک فایل اکسل برای پورتفولیوی سرمایه گذاری ایجاد کردم که به طور اتوماتیک قیمت ارز رو از سایت میگیره و در مقدار سرمایه ضرب میکنه و به طور لحظه ای یک پای چارت از مقدار و و درصد سرمایه میده.
    حالا من قصد دارم بتونم بهش زمان تعریف کنم تا به طور روزانه با توجه به قیمت یک line chart بده . یعنی هر روز قیمت جدید را اضافه کنه .
    چطور باید اینکارو انجام دهم؟

    1. حامد قدیمی

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

  8. ثریا محمودزاده

    با عرض سلام و خسته نباشید
    ی سوال داشتم
    صورت مسئله:
    مثلا بنده ۱۲ جلسه کلاس دارم فقط روزهای زوج، که از تاریخ ۹۶/۱۱/۱ شروع میشود.
    از چه تابعی می توانم استفاده کنم که روز و تاریخ را نشان من بدهد؟

    1. حامد قدیمی

      سلام، متشکرم، برای مشخص کردن روزهای زوج و فرد در اکسل تابعی وجود ندارد، با توجه به اینکه این مفهوم معادل International ندارد. لیکن برای انجام این کار باید به دنبال توابعی باشید که افراد متخصص توسعه دهنده نرم افزار (Developers) ایجاد کرده و به اشتراک گذاشته باشند. در غیر این صورت در اکسل راهی وجود ندارد.

  9. Hesam

    درود..من بخوام ساعات ماموریتم جمع بخوره به چه صورت باید عمل کنم یعنی مثلا من ساعت ۸ صبح ۹۶/۱۲/۲۶ رفتم ماموریت تا ۱۸ عصر ۹۶/۱۲/۲۸ .. با چه فرمولی باید ساعت رو با در نظر گرفتن تاریخ جمع زد؟

    1. حامد قدیمی

      سلام، زمان و تاریخ در اکسل عدد میباشند،به همین دلیل شما میتوانید با یک تفرق ساده اختلاف دو زمان را بدست آورید تنها نکته این است که فرمت سلول را طوری تغییر دهید که عدد بدست آمده به صورت ساعت نشان داده شود
      [H]

  10. عارفه اعلایی

    خسته نباشید. من یک ستون به اسم زمان سفر دارم. مثلا زمان سفر برابر ۰۰:۲۵:۳۵ می باشد به معنی ۲۵ دقیقه و ۳۵ ثانیه اگر بخواهم تبدیل کنم به ثانیه و خروجی ۳۰۳۵ ثانیه باشد چکار کنم؟

    1. حامد قدیمی

      سلام، شما ابتدا باید با استفاده از توابع توضیح داده شده در این مقاله مقدار ثانیه، ساعت و دقیقه را از مقدار نشان داده شده در فرمول استخراج نمایید و سپس با استفاده از یک فرمول ساده آن را به مقدار مورد نظر تفسیر کنید. از توابع MINUTE، SECOND , HOUR استفاده کنید. موفق باشید.

  11. hossein

    سلام و خداقوت
    یه سوال داشتم.میخواستم بهم کمک کنید با ذکر مثال که من چطوری دوتا تاریخ رو از هم تفریق کنم و تعداد روز رو بهم بده.مثلا:(۹۷۰۳۲۰ رو از تاریخ ۹۳۰۲۱۵ تفرق کنم و بهم تعداد روز بده ).همینطوری تفریق میکنم یه عددی میده که اشتباه هست.میخوام تفریق شون بهم تعداد روز بده.مثلا ۱۵۲۰ روز بشه تفریق شون مثلا.
    از چه تابع ایی و چگونه استفاده کنم ؟
    خیلی ممنون و دمتون گرم

    1. حامد قدیمی

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

  12. َAdel

    با سلام و تشکر از مطالب مفیدتون
    میخواستم یه ستون که به فرمت [H] هست رو به ستونی با فرمت جنرال General تقسیم کنم. با تقسیم ساده جواب صحیح نمیده. چطور میتونم این کار رو بکنم؟

    1. حامد قدیمی

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

  13. آزاده

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

    1. حامد قدیمی

      سلام، بستگی به آن تابعی دارد که از آن استفاده کردید، در این مقاله میتوانید توابع مختلف را به همراه کاربرد آن مطالعه نمایید.

  14. mohammad

    سلام میخواستم در اکسل ضمانتنامه ثبت کنم که ۱۰ روز مونده اعتبارش تموم بشه رنگش تغییر کنه میتونید کمکم کنید
    ممنون

  15. حسن

    سلام و وقت بخیر . در قسمتی از کار من تاریخ شش ماهه وجود دارد که اگر من تاریخ امروز رو وارد کنم در جای دیگر باید شش ماه دیگر را به من به فوریت نشان دهد . مثلا من اگر وارد کنم ۰۵/۰۹/۲۰۱۸ در جای دیگر به من ۰۵/۰۳/۲۰۱۹ را نشان دهد . لطفا فرمول را بفرمایید . متشکرم

  16. مریم

    سلام من دارم یک جدول برای کنترل پروژه در اکسل درس میکنم و مشکل ثبت تاریخ و روز برگزاری جلسات رو دارم . مشکل این هست که میخوام برای هر Task و متولی اون داخل جدولم زمان و روز جلسه رو در فرمم بیارم بدون اینکه دستی اونو وارد کنم . به صورتی که در تیتر جدول یک ستون برای روز یک ستون برای ماه داشته باشم و تاریخ ها در اون قسمت از قبل ثبت شده باشه و من اون رو فقط انتخاب کنم . یه چیزی مثل فیلتر کردن . آیا این قابلیت در اکسل وجود داره؟

    1. حامد قدیمی

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

یک دیدگاه بگذارید برای سهند برای لغو پاسخ اینجا کلیک کنید.