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

به علت طولانی شدن مطلب، توابع تاریخ و زمان در سه مقاله آموزش داده شده است، در مقاله ی اول به معرفی آن دسته از توابع که برای تبدیل یک Serial Number زمان به سال، ماه، روز، ساعت، دقیقه و ثانیه به کار برده میشدند پرداختیم، و در ادامه ی مقاله ی اول دو تابع برای دریافت زمان فعلی در اکسل معرفی کردیم. همچنین در مقاله اول با مفهوم تاریخ و ساعت در اکسل آشنا شدیم که کمک بسیاری به درک توابع تاریخ و زمان میکند، لذا توصیه میشود حتماً قسمت اول از مقاله توابع تاریخ و زمان را مطالعه نمایید.

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

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

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

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

توابعی برای بدست آوردن شماره هفته

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

تابع WEEKNUM در اکسل

اگر بخواهیم بدانیم که یک تاریخ خاص در چندمین هفته از آن سال اتفاق میافتد میتوانیم از تابع WEEKNUM استفاده کنیم، ساختار این تابع به صورت زیر است:

WEEKNUM (serial_number,[return_type])

در ساختار تابع WEEKNUM، آرگومان اول سریال نامبر (Serial Number) مربوط به یک تاریخ است، این آرگومان میتواند خروجی تابع DATE باشد، توصیه میگردد از درج تاریخ به فرمت Text استفاده نشود. آرگومان دوم اختیاری میباشد، این آرگومان یک عدد است که تعیین میکند چه روزی اولین روز هفته میباشد، به صورت پیشفرض و در صورت نادیده گرفتن آرگومان دوم اولین روز هفته روز یکشنبه خواهد بود، جدول زیر اعدادی که میتوان به عنوان آرگومان دوم قرار داد را نمایش میدهد:

تابع WEEKNUM در اکسل

لازم به ذکر است، خروجی تابع WEEKNUM یک عدد میباشد که نشان میدهد تاریخ مورد بررسی چندمین هفته از آن سال میباشد، برای شمارش هفته های یک سال دو سیستم وجود دارد، سیستم ۱ و سیستم ۲٫ سیستم اول فرض میکند هفته ای که روز اول ژانویه (January 1) در آن واقع شده است هفته ی اول سال میباشد و سیستم دوم هفته ی اول سال را هفته ای در نظر میگیرد که اولین پنجشنبه سال در آن واقع شده است، سیستم دوم روشی است که در ISO 8601 بیان شده که معمولاً تحت عنوان سیستم اروپایی شمارش هفته ها (European Week Numbering System) شناخته میشود.

تابع ISOWEEKNUM در اکسل

اگر آرگومان دوم تابع WEEKNUM را عدد ۲۱ (طبق جدول بالا) قرار دهیم خروجی تابع WEEKNUM و ISOWEEKNUM یکسان خواهد بود، در واقع خروجی تابع ISOWEEKNUM بر اساس سیستم ISO (سیستم دوم که در بالا توضیح داده شده) میباشد که روز اول هفته در آن دوشنبه میباشد. سینتکس این تابع به صورت زیر است:

ISOWEEKNUM (date)

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

توابع تاریخ و زمان

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

تابع DATE در اکسل

تابع DATE برای بدست آوردن Serial Number یک تاریخ خاص کاربرد دارد، تا اینجا با بسیاری از توابع تاریخ و زمان در اکسل آشنا شدیم، همانطور که میدانیم بسیاری از آرگومان های این دسته از توابع، سریال نامبر (Serial Number) تاریخ ها میباشد که با استفاده از تابع DATE میتوان آن را بدست آورد. سینتکس این تابع به صورت زیر است:

DATE (year,month,day)

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

  • اگر آرگومان اول عددی کوچکتر از ۱۹۰۰ (مبدا تاریخ در اکسل) باشد، اکسل آن عدد را بعلاوه ۱۹۰۰ کرده و به عنوان سال مورد نظر شما در نظر میگیرد. عدد سال باید عددی چهار رقمی بین ۱۹۰۰ و ۹۹۹۹ باشد.
  • لازم به ذکر است برای اعدادی خارج از اعداد مورد قبول اکسل برای سال، ماه و روز خروجی تابع خطای !NUM# خواهد بود.
  • عدد ماه از ۱ تا ۱۲ باید باشد، اگر عددی بزرگتر از دوازده به عنوان آرگومان دوم قرار گیرد، سال خروجی تابع به اندازه ای افزایش میابد که ماه های اضافه را جبران کند، همینطور اگر عدد ماه کمتر از یک یا منفی باشد سال خروجی تابع به قدی کاهش میابد که آن را جبران کند. با تمرین چند نمونه میتوانید این مطلب را بیشتر درک کنید.
  • عدد روز ماه هم باید عددی بین ۱ تا ۳۱ باشد، دقیقاً مشابه عدد ماه، اگر عدد روز خارج از بازه ی ۱ تا آخرین روز قابل قبول برای هر ماه باشد، عدد ماه خروجی تابع DATE به نحوی تغییر میکند که آن را جبران نماید.

تابع DATEVALUE در اکسل

این تابع برای تبدیل تاریخ که به فرمت Text نوشته شده به سریال نامبر (Serial Number) معادل با آن تاریخ کاربرد دارد. این تابع هنگامی کاربرد دارد که تاریخ های درج شده در فایل اکسل شما به صورت Text باشد، با استفاده از این تابع میتوان تاریخ ها را تبدیل به سریال نامبر نمود و بر روی آنها عملیات مورد نظر را انجام داد. سینتکس تابع DATEVALUE به صورت زیر است:

DATEVALUE (date_text)

آرگومان این تابع یک رشته متنی (یا آدرس یک سلول حاوی Text) میباشد که بیانگر یک تاریخ است مانند “۱/۳۰/۲۰۰۸″ یا “۳۰-Jan-2008″.

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

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

تابع TIME در اکسل

تابع TIME سریال نامبر (Serial Number) مربوط به ساعت مورد نظر را به شما میدهد. خروجی این تابع عددی بین صفر (معادل ساعت ۰۰:۰۰:۰۰) و عدد ۰٫۹۹۹۸۸۴۲۶ (معادل با ساعت ۲۳:۵۹:۵۹) میباشد. سینتکس تابع TIME به صورت زیر است:

TIME (hour, minute, second)

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

لازم به ذکر است اگر عدد ساعت بیش از ۲۳ باشد، باقیمانده تقسیم آن عدد بر ۲۴ بجای آرگومان اول قرار میگیرد و اگر اعداد دقیقه یا ثانیه بیش از ۵۹ باشد، عدد ساعت یا دقیقه به نحوی تغییر میکند که عدد وارد شده در بازه درست قرار گیرد، مثلاً اگر بجای آرگومان دوم عدد ۶۰ را بنویسید یک ساعت به آرگومان اول اضافه میگردد و آرگومان دوم صفر میشود.

هر یک از آرگومان های تابع TIME میتواند عددی بین صفر تا ۳۲۷۶۷ باشد.

تابع TIMEVALUE در اکسل

این تابع برای تبدیل ساعت هایی که به صورت رشته متنی (Text) نوشته شده اند به سریال نامبر (Serial Number) معادل با آن ساعت ها کاربرد دارد. سینتکس این تابع به صورت زیر است:

TIMEVALUE (time_text)

آرگومان این تابع یک رشته متنی میباشد که بیانگر یک ساعت از شبانه روز است، فرمت این متن میتواند مشابه “۶:۴۵ PM” یا “۱۸:۴۵″ باشد. اگر اطلاعات تاریخ همراه متن وارد شده وجود داشته باشد، تنها اطلاعات ساعت برداشت شده و بقیه در نظر گرفته نمیشود.

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

۶ نظر

  1. reyhane

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

    1. حامد قدیمی

      سلام، بله، Add-Ins هایی هستند که برای تبدیل تاریخ های شمسی و میلادی به یکدیگر استفاده میشوند. برای شما یک نمونه ارسال خواهم کرد. موفق باشید.

  2. طیبه راستی فر

    با سلام وخسته نباشید
    من با یک نرم افزار حسابداری کار می کنم که گزارش های خروجی تاریخ را بافونت ۹۵/۰۵/۳۱نشان می دهد و من برای اینکه بتوانم آمار درست ارائه کنم نیاز دارم جهت sortتاریخ فونت را به ۳۱/۰۵/۹۵تغییر دهم قبلا” فرمولی داشتم ولی الان نمی تونم پیداش کنم متاسفانه خیلی درگیر هست .
    خواهشمندم در صورت امکان کمکم کنید .
    سپاس

    1. حامد قدیمی

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

  3. امید

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

    1. حامد قدیمی

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

دیدگاهتان را بنویسید؟