آموزش تابع DATEVALUE در اکسل | آکادمی پویا حیاتی

معرفی تابع DATEVALUE

تابع DATEVALUE در اکسل برای تبدیل تاریخی که به شکل متن (Text) نوشته شده به عدد سریالی تاریخ استفاده می‌شود. اکسل تاریخ‌ها را در اصل به صورت عدد ذخیره می‌کند (Serial Number) تا بتواند روی آن‌ها محاسباتی مثل اختلاف روزها، فیلترهای زمانی، مقایسه تاریخ‌ها و… انجام دهد.

گاهی تاریخ‌ها از نرم‌افزارهای دیگر، فایل CSV، خروجی وب‌سایت‌ها یا سیستم‌های حسابداری وارد اکسل می‌شوند و به شکل متن می‌مانند. در این حالت ممکن است:

مرتب‌سازی درست عمل نکند
محاسبه اختلاف تاریخ‌ها خطا بدهد
COUNTIF و SUMIFS و… نتوانند شرط تاریخی را درست تشخیص دهند

DATEVALUE این متن را به یک تاریخ واقعیِ قابل محاسبه تبدیل می‌کند.

مثال ساده: فرض کنید در سلول A2 تاریخ به صورت متن نوشته شده: 1402/07/15 (یا یک تاریخ متنی مثل 2024/01/05). با DATEVALUE می‌توانید آن را به تاریخ قابل محاسبه تبدیل کنید.

=DATEVALUE(A2)

کاربردهای اصلی تابع DATEVALUE

تبدیل تاریخ‌های متنی وارد شده از CSV/سیستم‌های دیگر به تاریخ واقعی
رفع مشکل محاسبه اختلاف روزها، ماه‌ها و سال‌ها به خاطر متنی بودن تاریخ
یکسان‌سازی نوع داده تاریخ قبل از گزارش‌گیری و ساخت PivotTable
آماده‌سازی داده‌ها برای توابع شرطی مثل COUNTIF/COUNTIFS و SUMIFS
کمک به مرتب‌سازی و فیلتر صحیح تاریخ‌ها (وقتی اکسل آن‌ها را Text تشخیص داده)

ساختار (Syntax)

=DATEVALUE(date_text)

=DATEVALUE(متن_تاریخ)

آرگومان‌ها

date_text / متن_تاریخ

متنی است که تاریخ را نشان می‌دهد؛ می‌تواند:

ارجاع به سلول (مثل A2) که داخلش تاریخ به شکل متن نوشته شده
یک متن داخل کوتیشن (مثل “2024/01/05”)

خروجی DATEVALUE یک عدد است. برای اینکه آن را به شکل تاریخ ببینید باید فرمت سلول را Date کنید (Format Cells → Date) یا از قالب‌بندی سفارشی استفاده کنید.

مثال‌های ساده و پایه

مثال 1: تبدیل تاریخ متنی در یک سلول

فرض کنید سلول A2 شامل متن 2024/01/05 است و اکسل آن را Text می‌داند. در B2 بنویسید:

=DATEVALUE(A2)

نتیجه: یک عدد سریالی نمایش داده می‌شود. اگر فرمت B2 را Date کنید، تاریخ درست نمایش داده می‌شود.

مثال 2: تبدیل تاریخ نوشته شده داخل فرمول

=DATEVALUE("2024/03/21")

نتیجه: اکسل عدد سریالی متناظر با این تاریخ را برمی‌گرداند.

مثال 3: محاسبه اختلاف روزها بعد از تبدیل

فرض کنید A2 شامل “2024/01/01” و A3 شامل “2024/01/20” است (هر دو متنی). اختلاف روزها:

=DATEVALUE(A3)-DATEVALUE(A2)

نتیجه: 19

مثال‌های کاربردی و واقعی

مثال 1: جمع مبلغ فروش بین دو تاریخ (SUMIFS با تاریخ متنی)

فرض کنید:

ستون A: تاریخ سفارش (متنی)
ستون B: مبلغ
سلول E2: تاریخ شروع (متنی)
سلول E3: تاریخ پایان (متنی)

اگر تاریخ‌ها متنی باشند، بهتر است معیارها را به تاریخ واقعی تبدیل کنید. یک راه رایج این است که یک ستون کمکی بسازید (مثلاً C) و تاریخ واقعی را بسازید:

=DATEVALUE(A2)

سپس SUMIFS را روی ستون C اجرا کنید:

=SUMIFS(B:B;C:C;">="&DATEVALUE(E2);C:C;"<="&DATEVALUE(E3))

نتیجه: جمع مبلغ سفارش‌ها بین دو تاریخ.

مثال 2: شمارش سفارش‌ها بعد از یک تاریخ (COUNTIF)

فرض کنید ستون A تاریخ متنی و ستون C تاریخ تبدیل‌شده (با DATEVALUE) است و سلول E2 تاریخ مرجع متنی است:

=COUNTIF(C:C;">"&DATEVALUE(E2))

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

مثال 3: کنترل معتبر بودن تاریخ با AND و IF

فرض کنید تاریخ متنی در A2 است و می‌خواهید فقط اگر تاریخ در بازه سال 2024 باشد پیام “معتبر” بدهید:

=IF(AND(DATEVALUE(A2)>=DATEVALUE("2024/01/01");DATEVALUE(A2)<=DATEVALUE("2024/12/31"));"معتبر";"خارج از بازه")

مثال 4: جستجو بر اساس تاریخ (XLOOKUP) وقتی تاریخ‌ها متنی هستند

فرض کنید:

ستون A: تاریخ (متنی)
ستون B: نرخ ارز
سلول E2: تاریخ موردنظر (متنی)

راه استاندارد: یک ستون کمکی C بسازید که تاریخ واقعی باشد:

=DATEVALUE(A2)

سپس جستجو روی ستون C:

=XLOOKUP(DATEVALUE(E2);C:C;B:B;"یافت نشد")

ترکیب تابع DATEVALUE با فرمول‌های دیگر

ترکیب با IF برای اعتبارسنجی تاریخ متنی
```
=IF(DATEVALUE(A2)>TODAY();"تاریخ آینده";"اوکی")
```
ترکیب با TODAY برای محاسبه تعداد روز تا امروز
```
=TODAY()-DATEVALUE(A2)
```
ترکیب با COUNTIFS برای گزارش‌گیری بازه‌ای
```
=COUNTIFS(C:C;">="&DATEVALUE(E2);C:C;"<="&DATEVALUE(E3))
```
ترکیب با SUMIFS برای جمع شرطی بر اساس تاریخ
```
=SUMIFS(B:B;C:C;">="&DATEVALUE(E2);C:C;"<="&DATEVALUE(E3))
```
ترکیب با TEXT برای نمایش خروجی به شکل دلخواه (وقتی خروجی عددی است)
```
=TEXT(DATEVALUE(A2);"yyyy/mm/dd")
```

خطاهای رایج و روش رفع آنها

در استفاده از DATEVALUE چند خطای پرتکرار وجود دارد:

1) خطای #VALUE!

علت: متن تاریخ قابل تشخیص نیست (فرمت نامعتبر، کاراکتر اضافی، فاصله‌های پنهان، یا ترتیب روز/ماه مبهم).

راه‌حل:

فرمت ورودی را استاندارد کنید (مثلاً yyyy/mm/dd)
فاصله‌های اضافی را حذف کنید

=DATEVALUE(TRIM(A2))

2) تاریخ درست است ولی عدد نمایش داده می‌شود

علت: DATEVALUE خروجی را به صورت عدد سریالی برمی‌گرداند و سلول فرمت General دارد.

راه‌حل: فرمت سلول را Date کنید یا با TEXT نمایش دهید:

=TEXT(DATEVALUE(A2);"yyyy/mm/dd")

3) تاریخ‌ها تبدیل می‌شوند ولی با تقویم/منطقه تنظیمات سیستم مشکل دارند

علت: DATEVALUE به تنظیمات منطقه‌ای (Regional Settings) و نحوه تشخیص تاریخ وابسته است. اگر سیستم شما روز/ماه را متفاوت تشخیص دهد، ممکن است تاریخ اشتباه تفسیر شود.

راه‌حل:

تا حد ممکن از فرمت‌های واضح مثل yyyy/mm/dd استفاده کنید
اگر داده‌ها جداکننده‌های مختلف دارند، ابتدا یکدست‌سازی کنید (مثلاً Replace)

4) تاریخ شمسی (جلالی) و انتظار تبدیل مستقیم

علت: DATEVALUE به صورت پیش‌فرض تاریخ‌های شمسی را مثل تاریخ میلادی پردازش می‌کند و تبدیل شمسی به میلادی را انجام نمی‌دهد (مگر در شرایط خاصِ تقویم سیستم و ورودی‌ها که قابل اتکا نیست).

راه‌حل: اگر داده شما شمسی است، برای تبدیل دقیق باید از روش/تابع تبدیل جلالی به میلادی (VBA، پاورکوئری، یا فرمول‌های تبدیل) استفاده کنید و سپس DATEVALUE یا DATE روی خروجی میلادی اعمال شود.

نکات حرفه‌ای و ترفندهای مهم

ستون کمکی بسازید: اگر قرار است روی تاریخ‌ها فیلتر/گزارش بگیرید، بهتر است یک ستون “تاریخ واقعی” با DATEVALUE داشته باشید و همه محاسبات را روی آن انجام دهید.
ورودی را تمیز کنید: وجود فاصله‌های ابتدا/انتها یا کاراکترهای نامرئی بسیار رایج است؛ TRIM (و در صورت نیاز CLEAN) کمک می‌کند.
خروجی را فرمت کنید: DATEVALUE عدد می‌دهد؛ برای جلوگیری از سردرگمی، قالب تاریخ مناسب اعمال کنید.
فرمت‌های مبهم را حذف کنید: تاریخ‌هایی مثل 01/02/2024 ممکن است در برخی سیستم‌ها 1 فوریه و در برخی 2 ژانویه تفسیر شوند. فرمت yyyy/mm/dd ریسک را کم می‌کند.
در داده‌های حجیم: به‌جای تبدیل در هر فرمول شرطی، یک‌بار در ستون کمکی تبدیل کنید تا فایل سبک‌تر و سریع‌تر شود.

تفاوت تابع DATEVALUE با توابع مشابه

DATEVALUE در برابر DATE
- DATEVALUE: متن تاریخ را به تاریخ واقعی (عدد سریالی) تبدیل می‌کند.
- DATE: از سه عدد سال/ماه/روز یک تاریخ می‌سازد.
DATEVALUE در برابر VALUE
- VALUE: متن عددی را به عدد تبدیل می‌کند (گاهی برخی تاریخ‌ها را هم تبدیل می‌کند، اما قابل اتکا نیست).
- DATEVALUE: مخصوص تبدیل تاریخ متنی است و رفتارش برای تاریخ‌ها هدفمندتر است.
DATEVALUE در برابر TEXT
- TEXT: عدد/تاریخ واقعی را به متن با فرمت دلخواه تبدیل می‌کند.
- DATEVALUE: متن تاریخ را به تاریخ واقعی تبدیل می‌کند (جهت کاملاً برعکس TEXT).

سازگاری با نسخه‌های مختلف اکسل

تابع DATEVALUE جزو توابع قدیمی و پایه اکسل است و تقریباً در همه نسخه‌ها پشتیبانی می‌شود، از جمله:

Excel 2007، 2010، 2013، 2016، 2019، 2021
Microsoft 365
Excel برای Mac (با توجه به تنظیمات منطقه‌ای ممکن است تفسیر تاریخ متفاوت باشد)

نکته مهم در همه نسخه‌ها: نتیجه به تنظیمات منطقه‌ای و نحوه تشخیص تاریخ وابسته است؛ برای کم کردن خطا، ورودی را استاندارد کنید.

سؤالات پرتکرار درباره تابع DATEVALUE

آیا DATEVALUE خروجی را به صورت تاریخ نشان می‌دهد؟

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

اگر تاریخ من متنی نباشد، DATEVALUE لازم است؟

اگر اکسل تاریخ را واقعاً Date تشخیص داده باشد، نیازی نیست. DATEVALUE بیشتر برای زمانی است که تاریخ به شکل Text وارد شده.

DATEVALUE با تاریخ و ساعت چه می‌کند؟

DATEVALUE فقط بخش تاریخ را برمی‌گرداند. برای زمان معمولاً باید از TIMEVALUE یا روش‌های دیگر استفاده کنید.

چرا برای بعضی تاریخ‌ها نتیجه اشتباه می‌شود؟

معمولاً به خاطر فرمت مبهم (روز/ماه) یا تنظیمات منطقه‌ای سیستم است. فرمت yyyy/mm/dd را استفاده کنید.

جمع‌بندی و پیشنهاد یادگیری بعدی

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

برای یادگیری بعدی پیشنهاد می‌شود این موارد را دنبال کنید:

تابع DATE برای ساخت تاریخ از سال/ماه/روز
توابع TODAY و EDATE و EOMONTH برای کارهای زمان‌بندی
توابع SUMIFS و COUNTIFS برای گزارش‌گیری مبتنی بر تاریخ
Power Query برای تبدیل و پاکسازی تاریخ‌ها در داده‌های حجیم