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

در قسمت اول از مقاله “توابع تاریخ و زمان در اکسل” دانستیم که از این توابع میتوان در محاسبات مربوط به کار کردها، 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 عمل میکند.

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

تابع YEARFRAC در اکسل

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

YEARFRAC (start_date, end_date, [basis])

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

در صورتی که آرگومان سوم عددی خارج از جدول فوق باشد، خروجی تابع خطای !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 میباشد که در همین مقاله توضیح داده شده.