تابع DATE

تابع DATE در اکسل به شما کمک می‌کند تا یک تاریخ (روز، ماه و سال مشخص) را به همان شکل خاصی تبدیل کنید که اکسل می‌فهمد و معتبر می‌داند.

تاریخ در اکسل

✅ بگذارید مفهوم تاریخ معتبر در اکسل را بیشتر توضیح دهیم.

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

• اولین روز تاریخ در اکسل، برابر با 1 ژانویه 1900 یا 1/1/1900 است.
• بعد از این تاریخ، به ازای هر روز، یک عدد به مقدار قبلی اضافه می شود.
• یعنی 100 سال بعد، برای اولین روز سال 2000 به عدد 36525 (100 * 365 + 25) خواهید رسید (که 25 روز اضافه به دلیل سال های کبیسه در نظر گرفته شده است).

✍️ با اعداد سریالی اکسل می‌تواند محاسبات تاریخ را انجام دهد. محاسباتی چون:

1. تاریخ یک فاکتور
2. محاسبه زمان سررسید وام
3. محاسبه فاصله زمانی بین دو تاریخ
4. محاسبه سن فرد و …

? وقتی لیستی از تاریخ‌های معتبر داشته باشید آنگاه می‌توانید:

1. محاسبات تاریخ را انجام دهید و به کمک سایر توابع تاریخ در اکسل فرمول‌های پیچیده‌ای بسازید.
2. اطلاعات را بر مبنای تاریخ مرتب کنید.
3. اطلاعات را بر حسب تاریخ‌های خاصی فیلتر کنید و …

نقش تابع DATE

اگرچه نمایش تاریخ به صورت اعداد سریالی برای محاسبات مفید است، ولی این اعداد در واقع پشت صحنه محاسبات تاریخ هستند و کاربران با همان شکل ساده آن کار می‌کنند. یعنی اگرچه ما در می‌توانیم اولین روز سال 2000 را به صورت 1/1/2000 در ذهن‌مان تصور کنیم ولی کار کردن با شکل سریالی آن (یعنی 36525) برای ما مشکل است.

✅ در اینجا است که تابع DATE به کمک ما می آید. ?

شما به این تابع روز، ماه و سال دلخواه خود را می‌دهید و تابع DATE برای شما آن عدد سریالی را می‌سازد.

نمایش عدد سریالی در سلول

همان طور که گفتیم، خروجی تابع DATE یک عدد است (متن نیست). حالا وقتی این عدد سریالی درون یک سلول قرار می‌گیرد، می‌تواند نمایش‌های مختلفی به خود بگیرد که وابسته به قالب یا Format همان سلول است.

اگر Format آن سلول از جنس Number باشد، شما همان عدد سریالی را خواهید دید، ولی معمولاً Format سلول در حالت Date قرار می‌گیرد و شما همان سریال را به شکل یک تاریخ متنی (مثلاً 1/1/2000) می‌بینید. یعنی ظاهر سلول دارای جداکننده تاریخ یا "/" است ولی در اقع در پشت صحنه عدد است.

فرمت یک سلول را می‌توانید از روشهای مختلفی عوض کنید: یکی از این روشها، استفاده از تب Home و بخش Number است. روش دیگر کلیک راست روی سلول و انتخاب Format Cell است.

در ادامه با این تابع بیشتر آشنا می‌شوید.

نحوه نوشتن تابع DATE

این تابع را به شکل زیر می‌نویسیم:

=DATE(year, month, day)

• year: عدد مربوط به سال
• month: عدد مربوط به ماه
• day: عدد مربوط به روز

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

توجه کنید که:

• تاریخ 1/1/1900 معادل عدد 1 است.
• قبل از این تاریخ توسط این تابع پشتیبانی نمی‌شود ❗و در صورت نیاز باید از برنامه‌نویسی یا فرمول‌نویسی استفاده کنیم (که راه‌های آن در اینترنت قابل جستجو است و در اینجا به آن نمی‌پردازیم).
• هر یک واحد از اعداد طبیعی معادل یک روز است. البته واحدهای زمانی کوچکتر از 1 هم داریم که معادل ساعت و دقیقه و مانند آن هستند که در این تابع مدنظر نیست.
• اگر در ورودی مربوط به روز، عددی بزرگتر از 31 قرار دهید، تابع DATE هوشمندانه خودش روزهای اضافی را به طور خودکار به ماه بعد منتقل می کند. این کمک می‌کند که تا نگران محاسبات تاریخ نباشید و بتوانید آرگومان روز را به شکل پویا تعریف کنید.
• تابع DATE فقط ورودی عددی را می‌پذیرد و در صورتی که متن وارد شود، مقدار خطای #VALUE را برمی‌گرداند.

ورود اطلاعات در تابع DATE

شکل‌های مختلف ورود اطلاعات در تابع DATE در اینجا بررسی شده است:

اعداد ثابت

شما می‌توانید در ورودی‌های این تابع از اعداد ثابت صحیح استفاده کنید. برای مثال، تابع زیر عدد سریالی مربوط به روز اول سال 2000 را تولید می‌کند:

=DATE(2000,1,1)

صرفاً برای علاقه‌مندان:   بر حسب نیاز این اعداد می‌تواند مثبت یا منفی هم باشد. مثلاً در تابع بالا، اگر روز را صفر قرار دهید، خروجی تابع معادل 12/31/1999 و همچنین اگر روز 1- قرار دهید، خروجی تابع بالا معادل 12/30/1999 خواهد بود. استفاده از این موضوع بستگی به مسئله دارد. این موضوع برای آرگومان ماه هم صدق می‌کند.

ارجاع به سلول

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

مثلاً در تابع زیر، از ارجاع آرگومان روز به سلول A1 استفاده شده است:

=DATE(2000, 4, A1)

فرمول

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

محدوده‌ها، آرایه‌ها

در ورژن‌های جدید اکسل می‌توانید در ورودی این تابع از محدوده‌ها و آرایه‌ها نیز استفاده کنید، به شرط اینکه اعداد ورودی معتبر باشد. البته اگر همزمان می‌خواهید چندین ورودی را به شکل محدوده وارد کنید، لازم است از Ctrl Alt Enter استفاده کنید.

ترکیب تابع DATE با سایر توابع اکسل

در اینجا شکل‌هایی از کاربردها و ترکیب تابع DATE را از طریق چند مثال مرور می‌کنیم:

تعیین تاریخ 180 روز بعد

خروجی تابع DATE یک عدد است که می‌توانید آن را در محاسبات خود وارد کنید. مثلا در فرمول زیر به تاریخ مورد نظر عدد 180 را اضافه کرده‌ایم که معادل 180 روز بعد است:

=DATE(2000, 1, 1) + 180

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

تعیین تاریخ دقیق سال بعد

برای تولید ورود‌های تابع DATE می‌توانید از سایر توابع مربوط به تاریخ در اکسل استفاده کنید.

مثلا این فرمول تاریخ سلول A1 را یک سال به جلو می‌برد (با فرض اینکه A1 دارای یک تاریخ معتبر است):

=DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1))

در تابع بالا، ? مسیر ذهنی زیر را دنبال کنید.

1. تابع YEAR، عدد سال را از تاریخ موجود در سلول A1 استخراج می کند.
2. سپس عدد قبل با 1 جمع می‌شود. یعنی اکسل یک سال جلو می‌رود.
3. تابع MONTH، عدد ماه را از تاریخ موجود در سلول A1 استخراج می کند.
4. تابع DAY، عدد روز را از تاریخ موجود در سلول A1 استخراج می کند.
5. سپس تابع DATE از مقادیر حاصل از سه مرحله قبل، برای ایجاد یک تاریخ جدید استفاده می کند.

تغییر فرمت تاریخ به کمک تابع TEXT

با ترکیب تابع DATE و تابع TEXT می‌توان فرمت ظاهری نمایش تاریخ را عوض کرد.

? البته خروجی تابع TEXT متن است و دیگر عدد نیست و نمی‌توانید محاسبات تاریخی را با خروجی آن انجام دهید.

مثلاً فرمول زیر (که اقتباس از فرمول قبلی است)، تاریخ سال آینده را با فرمتی نشان می‌دهد که بین اعداد سال و ماه و روز یک خط فاصله (-) قرار گرفته است:

=TEXT(DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1)), "yyyy-mm-dd")

در فرمول بالا، قالب تعیین شده در انتهای تابع به صورت "yyyy-mm-dd" است. شما می‌توانید با تغییر جای dd و mm و yyyy، فرمت خروجی تابع را تغییر دهید. در اینجا به جزئیات قالب متن در تابع TEXT نمی‌پردازیم.

تعداد روز گذشته از یک تاریخ مشخص

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

تابع زیر تعداد روزهای گذشته از ابتدای سال 2000 را به شما می‌گوید:

=TODAY()-DATE(2000,1,1)

می‌توانید  ? مسیر ذهنی زیر را دنبال کنید:

1. تابع TODAY تاریخ امروز را برمی‌گرداند که مقداری سریالی است.
2. از طرفی اکسل عدد سریالی اولین روز سال 2000 را محاسبه می‌کند که طبیعتاً کمتر از مقدار عددی تاریخ امروز است.
3. و حالا تفاوت این دو در سلول نوشته خواهد شد که بر حسب روز است.

?? ادامه این مقاله درباره ترکیب تابع DATE با توابع شمارش شرطی (COUNTIF و COUNTIFS) و نیز توابع جمع شرطی (SUMIF و SUMIFS) است.

⛔ بهتر است قبل از ادامه، با این توابع بیشتر آشنا شوید چون در این مقاله خود این توابع را توضیح نمی‌دهیم.

سناریو‌هایی از ترکیب تابع DATE و توابع شمارش و جمع شرطی

✅ در اینجا چند سناریو را می‌خواهیم بیان کنیم تا با کاربردهای دیگری از تابع DATE آشنا شوید. در بخش بعدی مثال‌های مرتبط را خواهید دید.

✍️ برخی از توابع اکسل در درون خود از یک شرط استفاده می‌کنند که البته نحوه نوشتن این شرط بسته به تابع متفاوت است ولی در حالت کلی شما می‌توانید در آرگومان آنها به نوعی از تابع DATE استفاده کنید.

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

? فرض کنید می‌خواهید داده‌های فروش را به شکل روزانه و در تاریخ‌های مختلف دارید. سناریو‌های زیر را در نظر بگیرید:

• می‌خواهید همه آن فروش‌هایی را جمع کنید که:
  • در یک روز خاص رخ داده است.
  • پس از یک تاریخ خاص رخ داده است
  • یا در یک بازه تاریخی رخ داده است.
• یا می‌خواهید تعداد این روزها را بشمارید. یعنی روزهایی که آن رخدادهای فروش‌ را بشمارید که:
  • در یک روز خاص رخ داده است.
  • پس از یک تاریخ خاص رخ داده است
  • یا در یک بازه تاریخی رخ داده است.

برای پیاده‌سازی این سناریوها روشهای مختلفی وجود دارد ولی می‌توانید از توابع شمارش شرطی (COUNTIF و COUNTIFS) و نیز توابع جمع شرطی (SUMIF و SUMIFS) استفاده کنید و در آنها به کمک تاریع DATE یک معیار برای شمارش یا جمع ایجاد کنید.

◀️ نوشتن معیار در توابع SUMIF و COUNTIF

توجه کنید که اگر بخواهید که عمل جمع یا شمارش را با توجه به یک تاریخ حساب کنید (قبل یا بعد از یک تاریخ و یا در یک تاریخ مشخص)، نوشتن یک معیار شرطی کافی است پس از توابع SUMIF و COUNTIF استفاده می‌کنید.

وقتی یک تاریخ دقیق مطرح است

مثال زیر یک نمونه از این کار است. فرض کنید لیستی از تاریخ‌ها را داریم و می‌خواهیم آنهایی را بشماریم که تاریخ آنها دقیقاً اولین روز سال 2000 است (نه قبل و بعد از آن تاریخ، بلکه دقیقاً همان روز):

=COUNTIF(B2:B10, DATE(2000,1,1))

تابع بالا، محدوده B2 تا B10 را در نظر می‌گیرد و در آن سلول‌هایی را می‌شمارد که تاریخ آنها دقیقاً اولین روز سال 2000 است.

وقتی قبل یا بعد از یک تاریخ مطرح است

حالا فرض کنید می‌خواهید مجموع مبلغ فروش محصولاتی را حساب کنید که فروش آنها پس از 1 ژانویه 2000 انجام شده است.

فرض کنید ساختار داده‌های شما چنین است:

ستون B: شامل تاریخ فروش محصولات

ستون C: شامل قیمت فروش محصولات

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

">" & DATE(2000,1,1)

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

فرمول کامل به شکل زیر است:

=SUMIF(B2:B10,">"&DATE(2000,1,1),C2:C10)

در این مثال توجه کنید که:

1. B2:B10 محدوده‌ای است که می خواهید تاریخ ها را در آن بررسی کنید.
2. در ورودی بعدی تابع SUMIF، عبارت "بزرگتر از" به کمک & به فرمول DATE چسبیده است و معنی آن این است که تاریخ باید بزرگتر از 1 ژانویه 2000 باشد.
3. C2:C10 هم محدوده‌ای است که می‌خواهید مقادیر را در آن جمع کنید.

◀️ نوشتن معیار در توابع SUMIFS و COUNTIFS (همراه با S!)

حالا اگر محدوده‌ای از تاریخ‌ها را در نظر داشته باشید، از توابع SUMIFS و COUNTIFS استفاده می‌کنید. در اینجا این توابع را با جزئیات توضیح نمی‌دهیم و هدف صرفاً نمایش ترکیب DATE و این توابع برای نوشتن Criteria (معیار) در این توابع است.

فرمول زیر تعداد سلول‌هایی را می‌شمارد که تاریخ آن‌ها در بازه زمانی 1 ژانویه 2000 تا یک سال بعد از آن قرار دارد:

=COUNTIFS(B2:B10, ">="& DATE(2000,1,1) , B2:B10 , "<="& DATE(2001,1,1) )

در تابع بالا:

1. C2:C10: محدوده ای از سلول‌ها در ستون C که می‌خواهیم سلول‌های آن را بشماریم.
2. B2:B10: محدوده ای از سلول‌ها در ستون B که می‌خواهیم تاریخ‌ها را در آن بررسی کنیم.
3. با استفاده از تابع DATE بازه 1/1/2000 تا 1/1/2001 به عنوان محدوده شمارش تعریف شده است.

به طور مشابه می‌توانید مجموع مبلغ فروش را با تابع SUMIFS حساب کنید:

=SUMIFS( C2:C10, B2:B10, ">="&DATE(2000,1,1), B2:B10, "<="&DATE(2001,1,1))

جمع‌بندی

• از تابع DATE برای ایجاد یک تاریخ از اجزای جداگانه سال، ماه و روز استفاده می شود. در واقع این تابع یک داده تاریخی معتبر می‌سازد.
• خروجی این تابع یک عدد است که به صورت سریالی است و هر واحد آن معادل یک تاریخ مشخص است.
• از این تابع در محاسبات مربوط به تاریخ، مانند پیدا کردن اولین یا آخرین روز یک ماه، محاسبه تاریخ یک سال بعد یا دو سال قبل از تاریخ موجود در یک سلول و مانند آن استفاده می‌شود. این کار را می‌توانید با ترکیب DATE و سایر توابع دسته تاریخ در اکسل انجام دهید.
• نوشتن معیار یا Criteria در توابعی مانند SUMIF می‌تواند توسط تابع DATE انجام شود که حالات مختلف آن در این مقاله بررسی شد.

مهدی ملک