تابع DATEVALUE در اکسل — تبدیل متن به مقدار تاریخ

تابع DATEVALUE یکی از توابع پرکاربرد اکسل برای تبدیل متن (رشته) که نمایانگر یک تاریخ است به مقدار عددی تاریخ در اکسل است. خروجی این تابع یک عدد سریال (serial number) است که اکسل آن را به عنوان تاریخ می‌شناسد و می‌توان با فرمت‌دهی سلول، آن را به شکل خواناِ تاریخ نمایش داد.

قالب کلی و توضیح پارامتر

قالب تابع:

=DATEVALUE(date_text)

پارامتر date_text یک رشته (یا مرجع به سلولی که شامل متن تاریخ است) است. تابع تاریخ را بر اساس تنظیمات محلی (locale) سیستم یا نسخه اکسل تفسیر می‌کند.

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

• زمانی که ورودی یک متن با فرمت قابل‌قبول باشد:

=DATEVALUE("1/2/2020")

این فرمول مقدار سریال تاریخ 1 فوریه 2020 (یا 2 ژانویه 2020 بسته به تنظیم محلی) را برمی‌گرداند. برای دیدن آن به صورت خوانا، سلول را با فرمت تاریخ فرمت کنید.

=DATEVALUE(A2)

اگر در سلول A2 متن “2020-12-31” وجود داشته باشد، DATEVALUE مقدار تاریخ معادل را برمی‌گرداند.

نکات مهم درباره فرمت‌ها و منطقه (Locale)

• DATEVALUE بر اساس تنظیمات منطقه‌ای سیستم (Regional Settings) یا اکسل تعیین می‌کند که رشته “1/2/2020” معادل کدام ترکیب روز/ماه/سال است.
• در سیستم‌هایی که فرمت پیش‌فرض dd/mm/yyyy است، “1/2/2020” یعنی 1 فوریه؛ در سیستم‌های mm/dd/yyyy همان رشته یعنی 2 ژانویه.
• رشته‌هایی با نام ماه (مثل “Jan”, “February”) معمولاً بهتر تفسیر می‌شوند ولی باز هم به زبان سیستم وابسته هستند.

هنگام مواجهه با خطای #VALUE!

• اگر رشته قابل تفسیر نباشد، اکسل خطای #VALUE! برمی‌گرداند.
• علت‌های معمول: فرمت نامعمول، حروف اضافی، فاصله‌های نامناسب، یا تاریخ به صورت تقویم جلالی (هجری شمسی / Persian) که DATEVALUE آن را نمی‌شناسد.
• راهکارها: پاک‌سازی متن (SUBSTITUTE, TRIM), تبدیل اجزای تاریخ با LEFT/MID/RIGHT و سپس استفاده از تابع DATE، یا استفاده از Power Query یا ماکرو برای تبدیل تقویم جلالی به میلادی.

نمونه فرمول برای رشته با فرمت مشخص (dd/mm/yyyy)

اگر رشته شما همیشه در فرمت dd/mm/yyyy ذخیره شده، به جای اعتماد به locale می‌توانید اجزا را جدا کرده و با DATE بسازید:

=DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2))

فرض کنید A1 = “31/12/2020”. این فرمول سال را از 4 کاراکتر انتهایی می‌گیرد، ماه را از موقعیت 4 به طول 2 می‌گیرد و روز را از ابتدای رشته استخراج می‌کند؛ سپس تابع DATE یک مقدار تاریخ معتبر می‌سازد.

مثال: وقتی رشته شامل زمان هم هست

رشته‌هایی مانند “2020-02-01 14:30” شامل تاریخ و زمانند. تابع DATEVALUE فقط بخش تاریخ را برمی‌گرداند و TIMEVALUE بخش زمان را:

=DATEVALUE(LEFT(A1,10)) + TIMEVALUE(RIGHT(A1,5))

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

جداول نمونه ورودی و خروجی

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

• محاسبه اختلاف تاریخ: پس از تبدیل متن به تاریخ می‌توان از DATEDIF، NETWORKDAYS یا ساده‌ترین حالت A-B استفاده کرد.
• گزارش‌سازی: هنگام گرفتن داده از سیستم‌های مختلف که تاریخ را به صورت متن ارسال می‌کنند، ابتدا با DATEVALUE آن‌ها را تبدیل کنید تا امکان فیلتر و گروه‌بندی زمانی فراهم شود.
• پاک‌سازی خودکار: ترکیب DATEVALUE با IFERROR برای جلوگیری از خطا:

=IFERROR(DATEVALUE(A2), "Invalid Date")

این فرمول در صورت بروز خطا، متن “Invalid Date” را نمایش می‌دهد.

محدودیت‌ها و راه‌حل‌ها برای تاریخ‌های فارسی (جلالی)

تابع DATEVALUE تاریخ‌های هجری شمسی را مستقیماً تشخیص نمی‌دهد. برای داده‌های جلالی گزینه‌ها عبارت‌اند از:

• استفاده از Power Query برای تبدیل تقویم یا تغییر Locale هنگام خواندن متن (From Text/CSV → Locale).
• نوشتن تابع VBA (UDF) که تاریخ جلالی را به میلادی تبدیل کند، سپس خروجی را با DATEVALUE یا DATE استفاده کنید.
• استفاده از افزونه‌ها یا ابزارهای آماده تبدیل تقویم.

نکته: توضیح دقیق کدنویسی تبدیل جلالی به میلادی فراتر از محدوده این مقاله است؛ اما برای پروژه‌های جدی توصیه می‌شود از Power Query یا کتابخانه‌های معتبر استفاده شود.

نکات بهینه‌سازی و توصیه‌های کارشناسی

• همیشه قبل از استفاده از DATEVALUE داده‌های متنی را پاک‌سازی کنید (TRIM، SUBSTITUTE برای حذف کاراکترهای نامرئی یا تغییر جداکننده‌ها).
• اگر خروجی می‌خواهید قابل خواندن باشد، سلول را با فرمت تاریخ مناسب (Custom Date Format) تنظیم کنید.
• برای داده‌های وارد شده از منابع مختلف (CSV از سیستم‌های دیگر) از Power Query استفاده کنید چون کنترل بیشتری روی Locale و نوع داده خواهد داد.
• از تابع VALUE زمانی استفاده کنید که رشته حاوی عدد سریال باشد؛ اما برای تاریخ بهتر است از DATEVALUE یا ساخت DATE با اجزا استفاده شود.

خلاصه و جمع‌بندی

تابع DATEVALUE ابزار ساده و قدرتمندی برای تبدیل متن به تاریخ در اکسل است، اما باید به فرمت داده و تنظیمات منطقه‌ای توجه داشت. در مواردی که فرمت ثابت دارید، بهتر است با تجزیه رشته و تابع DATE مقدار دقیق‌تری بسازید. برای تاریخ‌های جلالی یا داده‌های پیچیده، Power Query یا ماژول‌های تبدیل تخصصی مناسب‌ترند.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال