نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 64

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال 1392 به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، طراحی گرافیک و برنامه نویسی NET. فعالیت دارم.

  • روش‌های محاسبه تاریخ

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

مفهوم و منطق محاسباتی تاریخ

تاریخ در اکسل، به 2 صورت تعیین می‌شود. تاریخ شمسی و تاریخ میلادی. تاریخ میلادی به صورت عدد ذخیره شده و مبنای محاسبات آن تاریخ 1900/01/01 می‌باشد که مقدار آن برابر با 1 است. مثلا عدد متناظر با تاریخ 2019/01/14 برابر با 43,479 می‌باشد و به معنی این است که این تعداد روز از تاریخ 1900/01/01 که مبنای تاریخ میلادی است گذشته است. اگر داده‌هایی که از جنس تاریخ هستند را به صورت General یا Number قالب‌بندی کنیم، به عدد تبدیل می‌شوند.

تاریخعدد
1900/01/011
1984/08/1930,913
2019/01/1443,479

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

توابع محاسبه تاریخ

تابع Day

تابع Day جزء توابع تاریخ و زمان می‌باشد که روز مربوط به یک تاریخ مشخص را فراخوانی می‌کند.

ساختار تابع

DAY(serial_number)
آرگومانشرح
serial_numberاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول یا تابعی از جنس تاریخ باشد.

آرگومان‌های تابع Day

ویژگی‌های تابع

  1. تاریخی که مستقیما در آرگومان serial_number تعریف می‌شود باید بین "" قرار بگیرد.
  2. چون تاریخ‌ها به صورت عدد ذخیره می‌شوند، آن عدد می‌تواند در آرگومان serial_number تعریف شود.
  3. اگر مقدار آرگومان serial_number خارج از مبنای محاسبات تاریخ باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید مقدار سلول A1 را برابر با تاریخ 2019/01/14 قرار داده‌ایم. تابع Day را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع مقدار روز از تاریخ 2019/01/14 را فراخوانی کرده و عدد 14 را به عنوان نتیجه برمی‌گرداند.

=DAY(A1)

تابع Month

تابع Month جزء توابع تاریخ و زمان می‌باشد که ماه مربوط به یک تاریخ مشخص را فراخوانی می‌کند.

ساختار تابع

MONTH(serial_number)
آرگومانشرح
serial_numberاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول یا تابعی از جنس تاریخ باشد.

آرگومان‌های تابع Month

ویژگی‌های تابع

  1. تاریخی که مستقیما در آرگومان serial_number تعریف می‌شود باید بین "" قرار بگیرد.
  2. چون تاریخ‌ها به صورت عدد ذخیره می‌شوند، آن عدد می‌تواند در آرگومان serial_number تعریف شود.
  3. اگر مقدار آرگومان serial_number خارج از مبنای محاسبات تاریخ باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید مقدار سلول A1 را برابر با تاریخ 2019/01/14 قرار داده‌ایم. تابع Month را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع مقدار ماه از تاریخ 2019/01/14 را فراخوانی کرده و عدد 1 را به عنوان نتیجه برمی‌گرداند.

=MONTH(A1)

تابع Year

تابع Year جزء توابع تاریخ و زمان می‌باشد که سال مربوط به یک تاریخ مشخص را فراخوانی می‌کند.

ساختار تابع

Year(serial_number)
آرگومانشرح
serial_numberاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول یا تابعی از جنس تاریخ باشد.

آرگومان‌های تابع Year

ویژگی‌های تابع

  1. تاریخی که مستقیما در آرگومان serial_number تعریف می‌شود باید بین "" قرار بگیرد.
  2. چون تاریخ‌ها به صورت عدد ذخیره می‌شوند، آن عدد می‌تواند در آرگومان serial_number تعریف شود.
  3. اگر مقدار آرگومان serial_number خارج از مبنای محاسبات تاریخ باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید مقدار سلول A1 را برابر با تاریخ 2019/01/14 قرار داده‌ایم. تابع Year را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع مقدار سال از تاریخ 2019/01/14 را فراخوانی کرده و عدد 2019 را به عنوان نتیجه برمی‌گرداند.

=YEAR(A1)

بیشتر بخوانید: رده‌بندی مقادیر عددی

تابع Date

تابع Date جزء توابع تاریخ و زمان می‌باشد که یک تاریخ میلادی را به عنوان نتیجه تابع برمی‌گرداند.

ساختار تابع

DATE(year,month,day)
آرگومانشرح
yearاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد یا آدرس سلول باشد. سال مربوط به تاریخ میلادی در این آرگومان تعیین می‌شود.
monthاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد یا آدرس سلول‌ باشد. ماه مربوط به تاریخ میلادی در این آرگومان تعیین می‌شود.
dayاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد یا آدرس سلول‌ باشد. روز مربوط به تاریخ میلادی در این آرگومان تعیین می‌شود.

آرگومان‌های تابع Date

ویژگی‌های تابع

  1. اگر مقدار آرگومان year بین صفر تا 1899 باشد، برای محاسبه سال این مقدار با 1900 جمع می‌شود.
  2. اگر مقدار آرگومان year بین 1900 تا 9999 باشد، این مقدار به عنوان سال در نظر گرفته می‌شود.
  3. اگر مقدار آرگومان year کوچکتر از صفر یا بزرگتر یا مساوی 10000 باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
  4. اگر مقدار آرگومان month بزرگتر از 12 باشد، این مقدار به اولین ماه در آرگومان year اضافه می‌شود.
  5. اگر مقدار آرگومان month کوچکتر از 1 باشد، این مقدار از اولین ماه در آرگومان year کم شده و با عدد 1 جمع می‌شود.
  6. اگر مقدار آرگومان day بزرگتر از تعداد روز‌های آرگومان month باشد، این مقدار به اولین روز در آرگومان month اضافه می‌شود.
  7. اگر مقدار آرگومان day کوچکتر از 1 باشد، این مقدار از اولین روز در آرگومان month کم شده و با عدد 1 جمع می‌شود.

آرگومان year می‌تواند بین 1 تا 4 رقم باشد، اما بهتر است این آرگومان را با 4 رقم تعیین کنیم. مثلا مقدار 07 در این آرگومان می‌تواند به معنای 1907 یا 2007 باشد. سال‌های 4 رقمی مانع ایجاد اشتباه در محاسبات می‌شوند.

مثال

فرض کنید می‌خواهیم مقدار سلول‌های A2، B2 و C2  را با هم ترکیب کرده و آنها را به عنوان تاریخ در سلول D2 قرار دهیم. تابع Date را به صورت زیر در سلول D2 تعریف می‌کنیم. این تابع مقدار سلول A2 را به عنوان سال، مقدار سلول B2 را به عنوان ماه و مقدار سلول C2 را به عنوان روز در نظر گرفته و تاریخ 2019/01/14 را به عنوان نتیجه برمی‌گرداند.

=DATE(A2,B2,C2)

محاسبه تاریخ میلادی با تابع Date

محاسبه تاریخ میلادی با تابع Date

تابع Datevalue

تابع Datevalue جزء توابع تاریخ و زمان می‌باشد که یک رشته نشان‌دهنده تاریخ را به یک داده از جنس تاریخ تبدیل می‌کند.

ساختار تابع

DATEVALUE(date_text)
آرگومانشرح
date_textاختصاص این آرگومان ضروری بوده و می‌تواند شامل رشته یا آدرس سلول باشد.

آرگومان‌های تابع Datevalue

ویژگی‌های تابع

  1. اکثر توابع به صورت خودکار رشته‌هایی که نشان‌دهنده تاریخ باشند را به یک داده از جنس تاریخ تبدیل می‌کنند.
مثال

فرض کنیم می‌خواهیم رشته 2019/01/14 را به یک داده از جنس تاریخ تبدیل کنیم. تابع Datevalue را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع عدد 43,479 که متناظر با تاریخ 2019/01/14 می‌باشد را به عنوان نتیجه برمی‌گرداند.

=DATEVALUE("14/01/2019")

تابع Weeknum

تابع Weeknum جزء توابع تاریخ و زمان می‌باشد که شماره هفته مربوط به یک تاریخ مشخص را فراخوانی می‌کند.

ساختار تابع

WEEKNUM(serial_number,[return_type])
آرگومانشرح
serial_numberاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول یا تابعی از جنس تاریخ باشد.
return_typeاختصاص این آرگومان‌ اختیاری بوده و می‌توانند شامل عدد باشد. اولین روز هفته در این آرگومان تعیین می‌شود.

آرگومان‌های تابع Weeknum

مقدارشرح
1روز یک‌شنبه را به عنوان اولین روز هفته تعیین می‌کند.
2روز دوشنبه را به عنوان اولین روز هفته تعیین می‌کند.
11روز دوشنبه را به عنوان اولین روز هفته تعیین می‌کند.
12روز سه‌شنبه را به عنوان اولین روز هفته تعیین می‌کند.
13روز چهارشنبه را به عنوان اولین روز هفته تعیین می‌کند.
14روز پنج‌شنبه را به عنوان اولین روز هفته تعیین می‌کند.
15روز جمعه را به عنوان اولین روز هفته تعیین می‌کند.
16روز شنبه را به عنوان اولین روز هفته تعیین می‌کند.
17روز یک‌شنبه را به عنوان اولین روز هفته تعیین می‌کند.
21روز دوشنبه را به عنوان اولین روز هفته تعیین می‌کند. این مقدار طبق استاندارد 8601 عمل می‌کند، که معمولا به عنوان سیستم شماره‌گذاری اروپایی شناخته می‌شود.

مقادیر آرگومان return_type در تابع Weeknum

ویژگی‌های تابع

  1. تاریخی که مستقیما در آرگومان serial_number تعریف می‌شود باید بین "" قرار بگیرد.
  2. چون تاریخ‌ها به صورت عدد ذخیره می‌شوند، آن عدد می‌تواند در آرگومان serial_number تعریف شود.
  3. پیش‌فرض آرگومان return_type عدد 1 می‌باشد.
  4. اگر مقدار آرگومان serial_number خارج از مبنای محاسبات تاریخ باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
  5. اگر مقدار آرگومان return_value خارج از مقادیر مشخص شده در جدول 8 باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید مقدار سلول A1 را برابر با تاریخ 2019/01/14 قرار داده‌ایم. تابع Weeknum را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع شماره هفته مربوط به تاریخ 2019/01/14 را فراخوانی کرده و عدد 3 را به عنوان نتیجه برمی‌گرداند.

=WEEKNUM(A1)

بیشتر بخوانید: ابزار جدول (Table)

تابع Weekday

تابع Weekday جزء توابع تاریخ و زمان می‌باشد که روز هفته مربوط به یک تاریخ مشخص را فراخوانی می‌کند.

ساختار تابع

WEEKDAY(serial_number,[return_type])
آرگومانشرح
serial_numberاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول یا تابعی از جنس تاریخ باشد.
return_typeاختصاص این آرگومان‌ اختیاری بوده و می‌توانند شامل عدد باشد. مقداری که در نتیجه تابع برگردانده می‌شود در این آرگومان تعیین می‌شود.

آرگومان‌های تابع Weekday

مقدارشرح
1اعداد 1 (یک‌شنبه) تا 7 (شنبه) را به عنوان نتیجه تابع برمی‌گرداند.
2اعداد 1 (دوشنبه) تا 7 (یک‌شنبه) را به عنوان نتیجه تابع برمی‌گرداند.
3اعداد صفر (یک‌شنبه) تا 6 (شنبه) را به عنوان نتیجه تابع برمی‌گرداند.
11اعداد 1 (دوشنبه) تا 7 (یک‌شنبه) را به عنوان نتیجه تابع برمی‌گرداند.
12اعداد 1 (سه‌شنبه) تا 7 (دوشنبه) را به عنوان نتیجه تابع برمی‌گرداند.
13اعداد 1 (چهارشنبه) تا 7 (سه‌شنبه) را به عنوان نتیجه تابع برمی‌گرداند.
14اعداد 1 (پنج‌شنبه) تا 7 (چهارشنبه) را به عنوان نتیجه تابع برمی‌گرداند.
15اعداد 1 (جمعه) تا 7 (پنج‌شنبه) را به عنوان نتیجه تابع برمی‌گرداند.
16اعداد 1 (شنبه) تا 7 (جمعه) را به عنوان نتیجه تابع برمی‌گرداند.
17اعداد 1 (یک‌شنبه) تا 7 (شنبه) را به عنوان نتیجه تابع برمی‌گرداند.

مقادیر آرگومان return_type در تابع Weekday

ویژگی‌های تابع

  1. تاریخی که مستقیما در آرگومان serial_number تعریف می‌شود باید بین "" قرار بگیرد.
  2. چون تاریخ‌ها به صورت عدد ذخیره می‌شوند، آن عدد می‌تواند در آرگومان serial_number تعریف شود.
  3. پیش‌فرض آرگومان return_type عدد 1 می‌باشد.
  4. اگر مقدار آرگومان serial_number خارج از مبنای محاسبات تاریخ باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
  5. اگر مقدار آرگومان return_value خارج از مقادیر مشخص شده در جدول 10 باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید مقدار سلول A1 را برابر با تاریخ 2019/01/14 قرار داده‌ایم. تابع Weekday را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع شماره هفته مربوط به تاریخ 2019/01/14 را فراخوانی کرده و عدد 2 که متناظر با روز دوشنبه می‌باشد را به عنوان نتیجه برمی‌گرداند.

=WEEKDAY(A1)

اگر قالب سلولی که تابع Weekday در آن تعریف شده است را به روی Custom تنظیم کنیم، به دو صورت می‌توانیم نام روز هفته را به جای عدد 2 در مثال بالا به عنوان نتیجه برگردانیم. الگوی ddd مقدار Mon و الگوی dddd مقدار Monday را در سلول نشان می‌دهد.

تابع Today

تابع Today جزء توابع تاریخ و زمان بوده و فاقد آرگومان می‌باشد. این تابع تاریخ جاری سیستم را به عنوان نتیجه تابع برمی‌گرداند.

ساختار تابع

TODAY()

ویژگی‌های تابع

  1. این تابع جزء توابع Volatile می‌باشد.

تابع Now

تابع Now جزء توابع تاریخ و زمان بوده و فاقد آرگومان می‌باشد. این تابع تاریخ و ساعت جاری سیستم را به عنوان نتیجه تابع برمی‌گرداند.

ساختار تابع

NOW()

ویژگی‌های تابع

  1. این تابع جزء توابع Volatile می‌باشد.

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

تابع Days

تابع Days جزء توابع تاریخ و زمان می‌باشد که تعداد روزهای بین دو تاریخ را محاسبه می‌کند.

ساختار تابع

DAYS(end_date,start_date)
آرگومانشرح
end_dateاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول یا تابعی از جنس تاریخ باشد. تاریخ انتهای بازه در این آرگومان تعیین می‌شود.
start_dateاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول‌ یا تابعی از جنس تاریخ باشد. تاریخ ابتدای بازه در این آرگومان تعیین می‌شود.

آرگومان‌های تابع Days

ویژگی‌های تابع

  1. اگر آرگومان‌ها به صورت عدد تعریف شوند، حاصل تفریق دو عدد به عنوان نتیجه تابع برگردانده می‌شود.
  2. اگر یکی از آرگومان‌ها دارای رشته نشان دهنده تاریخ باشد، با آن آرگومان به صورت Datevalue(date_text) رفتار می‌شود.
  3. اگر مقدار آرگومان‌ها خارج از مبنای محاسبات تاریخ باشند، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
  4. اگر مقدار آرگومان‌ها دارای رشته‌هایی باشند که نتوانند به تاریخ تبدیل شوند ، خطا #Value! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید می‌خواهیم فاصله بین 2 تاریخ را محاسبه کنیم. تابع Days را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع تاریخ 1984/08/19 را از تاریخ 2019/01/14 کم کرده و مقدار 12,566 را به عنوان نتیجه برمی‌گرداند.

=DAYS(B2,A2)

محاسبه فاصله بین دو تاریخ با تابع Days

محاسبه فاصله بین دو تاریخ با تابع Days