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

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

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

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

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

توابعی برای بدست آوردن روزهای بین دو تاریخ

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

تابع DAYS در اکسل

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

DAYS(end_date, start_date)

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

  • آرگومان ها میتوانند به صورت عدد (Serial Number) باشند که در این صورت خروجی تابع حاصل تفریق دو عدد است.

DAYS (21; 13) =8

  • آرگومان ها میتوانند به صورت Text هم باشند، و حتی میتوان یکی از آرگومان ها را Text و دیگری Serial Number باشد.

DAYS (“January 21, 1900”; ۱۳) =۸

DAYS (“۰۱/۲۱/۱۹۰۰”; “۰۱/۱۳/۱۹۰۰”) =۸

  • اگر آرگومان ها خارج از بازه زمانی تعریف شده در اکسل باشند خروجی تابع خطای NUM# خواهد بود.
  • اگر آرگومان های تابع عبارات متنی (Text) باشند که نتوانند به صورت تاریخ تعبیر شوند، خروجی تابع خطای !VALUE# خواهد بود.

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

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

تابع DAYS360 در اکسل

در برخی از سیستم های حسابداری یک سال را متشکل از دوازده ماه سی روزه فرض میکنند. تابع DAYS360 دقیقاً همان کاربرد تابع DAYS را دارد با این تفاوت که فرض محاسبه تعداد روزها، سیستم سال ۳۶۰ روزه میباشد. سینتکس این تابع به صورت زیر است:

DAYS360 (start_date,end_date,[method])

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

آرگومان سوم یک مقدار منطقی (Logical) است که تعیین میکند شما از روش آمریکایی (U.S Method) یا اروپایی (European Method) برای محاسبه استفاده میکنید، در صورت True بودن روش اروپایی و در صورت خالی ماندن و یا False بودن روش آمریکایی (روش پیشفرض) مورد استفاده قرار میگیرد.

  • در روش آمریکایی یا  NASD،اگر Starting Date (روز شروع) روز آخر ماه باشد، تبدیل به روز سی ام همان ماه میگردد. همینطور در این روش اگر Ending Date (روز پایان) روز آخر ماه باشد، در صورتی که روز شروع، روزی قبل از سی ام ماه باشد، تاریخ پایان تبدیل به روز اول ماه آتی میگردد و در غیر اینصورت روز پایان تبدیل به روز سی ام میگردد.
  • در روش اروپایی، روز شروع و یا پایانی که در روز سی و یکم یک ماه واقع شوند تبدیل به روز سی ام همان ماه میشوند.

تابع NETWORKDAYS در اکسل

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

NETWORKDAYS (start_date, end_date, [holidays])

دو آرگومان اول مانند توابع قبلی، تاریخ های شروع و پایان دوره میباشند. آرگومان سوم یک آرگومان اختیاری است و شامل تاریخ روزهای تعطیلی (تعطیلاتی غیر از تعطیلات آخر هفته) میباشد، در آمریکا، تعطیلاتی مانند Federal or State Holidays را به عنوان آرگومان سوم قرار میدهیم، این آرگومان میتواند آدرس یک محدوده از اکسل که این تاریخ ها در آنها درج شده و یا یک آرایه دربردارنده Serial Number مربوط به این تاریخ ها باشد.

تابع NETWORKDAYS.INT در اکسل

این تابع مشابه تابع NETWORKDAYS عمل میکند، با این تفاوت که در تابع NETWORKDAYS.INT به کاربر این امکان داده میشود که روزها و تعداد روزهای تعطیلات آخر هفته را تعیین نماید. ساختار این تابع به صورت زیر است:

NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays])

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

تابع NETWORKDAYS در اکسل

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

تابع YEARFRAC در اکسل

این تابع تعیین میکند که تعداد روزهای قرار گرفته بین دو تاریخ چه کسری از یک سال را شامل میشود، به عنوان یک نمونه از کاربرد تابع YEARFRAC، فرض کنید قرار است مبلغی به ازای هر سال فعالیت یک نفر به وی پرداخت شود، با استفاده از این تابع با توجه به تاریخ شروع و پایان کار آن شخص میتوان فهمید شخص مزبور چه کسری از یک سال را کار کرده و بالطبع چه کسری از مبلغ ذکر شده به او تعلق میگیرد. Syntax این تابع به صورت زیر است:

YEARFRAC (start_date, end_date, [basis])

با آرگومان اول و دوم آشنا هستیم، آرگومان سوم یک آرگومان اختیاری است که نوع شمارش روزها را تعیین میکند، با توجه به جدول زیر و برحسب سیستم مورد استفاده (روش U.S یا روش European) عدد مورد نظر را به عنوان آرگومان سوم قرار دهید:

تابع YEARFAC در اکسل

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

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

بدست آوردن تاریخ های قبل یا بعد از یک تاریخ

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

تابع EDATE در اکسل

اگر میخواهید تاریخ روزی مشابه یک روز خاص در یک ماه را در چند ماه آینده یا گذشته بدست بیاورید میتوانید از این تابع استفاده کنید، ساختار تابع EDATE به صورت زیر است:

EDATE (start_date, months)

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

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

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

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

تابع EOMONTH در اکسل

این تابع همانند تابع EDATE عمل میکند، با این تفاوت که خروجی این تابع Serial Number مربوط به روز آخر (End Of Month) چند ماه بعد و یا چند ماه قبل از یک تاریخ مشخص خواهد بود، Syntax تابع EOMONTH به صورت زیر است:

EOMONTH (start_date, months)

اگر آرگومان اول و یا خروجی تابع EOMONTH در بازه تاریخ مورد قبول اکسل نباشد خروجی تابع خطای !NUM# خواهد بود.

تابع WORKDAY در اکسل

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

WORKDAY (start_date, days, [holidays])

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

تابع WORKDAY.INT در اکسل

این تابع دقیقاً مانند تابع WORKDAY عمل میکند با این تفاوت که در این تابع مشابه آنچه در تابع NETWORKDAYS.INT مشاهده کردیم، میتوان روزهای تعطیل آخر هفته را مشخص کرد، لطفاً بخش مربوط به تابع NETWORKDAYS.INT را در همین مقاله حتماً مطالعه کنید.

ساختار تابع WORKDAYS.INT به صورت زیر است:

WORKDAY.INTL (start_date, days, [weekend], [holidays])

آرگومان سوم این تابع دقیقاً مانند آرگومان سوم تابع NETWORKDAYS.INT میباشد که در همین مقاله توضیح داده شده.

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

۲۰ نظر

  1. علی رحمانی

    با سلام
    به نظر می رسد تابع EOmonth برای تاریخ شمسی قابل استفاده نمی باشد آیا راه حلی برای این مشکل وجود دارد.
    با تشکر

    1. حامد قدیمی

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

  2. مصطفی

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

    راه ساده تری وجود دارد که مجبور نباشم دستی کاری انجام دهم؟

    1. حامد قدیمی

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

  3. عباسعلی شاه حسینی

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

    1. حامد قدیمی

      سلام، لطفاً به ساختار تابع در اکسل خود دقت نمایید. ببینید خود اکسل آرگومان ها توسط چه کاراکتری از هم جدا کرده است. اینکه آن کاراکتر سیمکولون باشد یا ویرگول تنظیمات ویندور است و ارتباطی به اکسل ندارد. در تنظیمات ویندوز در قسمت Language & Region قسمتی برای تعیین Separator وجود دارد که میتوانید آن را تغییر دهید. موفق باشید.

    1. حامد قدیمی

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

  4. راحله

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

    1. حامد قدیمی

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

  5. عباس

    آیا فرمولی برای محاسبه ساعت هست؟
    مثلا ساعت شروع ۲۱:۰۰ وساعت پایان ۰۱:۲۰نیمه شب میباشد لطفا نحوه محاسبه این طبق چه فرمولی هست؟

  6. saeed

    سلام،
    من میخوام به عنوان مثال تاریخ ۹۷۰۹۱۰ رو در که در یک سلول A1 ثبت شده با سلول B1 که برابر با ۱۲۰ هست جمع ببندم و نتیجه در سلول C1 خود تابع برام ۱۲۰ روز پس از تاریخ سلول A1نشان بده که میشه مثال ۹۸۰۱۰۹
    ممنون میشم اگه جواب رو برام ایمیل بفرمائید.

    یکدنیا سپاس

  7. موسوی

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

    1. حامد قدیمی

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

  8. سعید

    سلام
    من میخام یک تاریخ شروع بازه و یک تاریخ پایان بازه رو به اکسل بدم،بعد خروجی این باشه که تعداد روز هایی که در این بازه در سال ۹۵،۹۶ ۹۷ و ۹۸ بوده رو بهم بده. مثلا بگم شروع بازه از ۵ اردیبهشت ۹۶ بوده و انتهای بازه ۱۲ اسفند ۹۷. به این ترتیب بهم بگه که در سال ۹۶ در بازه مورد نظر ۳۳۰ روز داشتم سال ۹۷ هم ۳۳۲ روز و برای سال ۹۵ و ۹۸ صفر روز جواب بده
    با تشکر

    1. حامد قدیمی

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

  9. علی

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

    1. حامد قدیمی

      سلام، برای آلارم دادن و یا پیام داد حتماً باید ماکرو نویسی انجام بدهید و اکسل به تنهایی ممکن نیست، ابزاری که در اکسل میتواند سلولها با شرایط خاص (مثلاً سلول های حاوی اریخ نزدیک به تاریخ مورد نظر) را به صورت اتوماتیک تغییر فرمت دهد که مشخص باشد ابزار Conditional Formatting است. موفق باشید.

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