تبدیل متن به تاریخ در اکسل – به زبان ساده (+ دانلود فیلم آموزش رایگان)

تحلیل داده‌های کسب و کار در اغلب موارد نیازمند کار کردن با مقادیر تاریخ در اکسل است و باید به سؤال‌هایی مانند «امروز چه مقدار درآمد داشتیم؟» یا «مقدار امروز با دیروز چه تفاوتی دارد؟» پاسخ دهیم. یافتن پاسخ این سؤال‌ها در صورتی که اکسل نتواند مقادیر تاریخ را تشخیص دهد دشوار خواهد بود. متأسفانه این وضعیت یعنی عدم تشخیص صحیح مقادیر تاریخ در موارد مختلف در اکسل اتفاق می‌افتد و به خصوص زمانی که چندین کاربر اطلاعات را وارد کرده باشند و آن‌ها را از سیستم‌های دیگر کپی کرده و چسبانده باشند و یا از پایگاه‌های داده ایمپورت کرده باشند به وفور مشاهده می‌شود. در این مطلب با روش تبدیل متن به تاریخ در اکسل آشنا خواهیم شد.

تاریخ‌هایی که شامل نقطه هستند

احتمالاً یکی از رایج‌ترین خطاهایی که افراد تازه‌کار در زمان وارد کردن تاریخ در اکسل مرتکب می‌شوند این است که از کاراکتر نقطه (.) برای جداسازی روز، ماه و سال استفاده می‌کنند. اکسل این مقادیر را به عنوان تاریخ شناسایی نمی‌کند و آن را به صورت متنی ذخیره می‌سازد. با این حال، می‌توان این مشکل را با ابزار Find and Replace حل کرد. اکسل با جایگزینی علامت ممیز (/) به جای نقطه، به صورت خودکار مقادیر را به صورت تاریخ شناسایی می‌کند.

بدین منظور ابتدا ستون‌هایی را که می‌خواهید عملیات Find and Replace را روی آن‌ها اجرا کنید، انتخاب نمایید.

به مسیر Home > Find & Select > Replace بروید و یا کلیدهای Ctrl+H کیبورد را بزنید.

در پنجره Find and Replace یک نقطه در فیلد «Find what» وارد کنید و در فیلد «Replace with» نیز یک ممیز وارد نمایید. سپس روی «Replace All» کلیک کنید.

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

اگر داده‌های صفحه گسترده به طور معمول در حال تغییر یافتن هستند و می‌خواهید در این حالت از یک راه‌حل خودکار استفاده کنید می‌توانید از تابع SUBSTITUTE استفاده کنید.

=VALUE(SUBSTITUTE(A2،"."،"/"))

تابع SUBSTITUTE یک تابع متنی است و از این رو که نمی‌تواند خودش مقادیر را به تاریخ تبدیل کند. تابع VALUE مقدار متنی را به مقدار عددی تبدیل می‌کند. نتایج در تصویر زیر مشاهده می‌شوند. چنان که می‌بینید مقدار مربوطه به صورت یک تاریخ قالب‌بندی شده است.

این کار با استفاده از لیست Number Format در برگه Home میسر است.

مثال زیر در مورد جداکننده نقطه کاملاً مشهور است، اما می‌توان از همین تکنیک برای جایگزینی هر کاراکتر جداکننده دیگری استفاده کرد.

تبدیل قالب yyyymmdd

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

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

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

• تبدیل متن به ستون در اکسل — راهنمای گام به گام

بازه مقادیری که باید تبدیل شوند را انتخاب کنید و سپس به مسیر Data > Text to Columns مراجعه کنید.

بدین ترتیب ویزارد Text to Columns ظاهر می‌شود. روی Next در دو گام اول کلیک کنید تا به گام سوم برسید که در تصویر زیر نمایش یافته است. تاریخ را انتخاب کنید و سپس قالب داده‌ای را که در سلول‌های لیست استفاده می‌شود انتخاب کنید. در این مثال، ما با قالب YMD سروکار داریم.

اگر می‌خواهید از یک راه‌حل فرمولی استفاده کنید، در این صورت باید از تابع Date به ساختن تاریخ بهره بگیرید. این وضعیت همراه با تابع‌های متنی Left ،Mid و Right برای استخراج سه بخش از یک تاریخ (روز، ماه، سال) از محتوای سلول استفاده می‌شود. فرمول زیر پیاده‌سازی راه‌حل پیشنهادی برای داده‌های نمونه ما است.

=DATE(LEFT(A2،4)،MID(A2،5،2)،RIGHT(A2،2))

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

تابع‌های DATEVALUE و VALUE

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

در ادامه لیستی از تاریخ‌ها را با ساختارهای متنوع مشاهده می‌کنید که گرچه ما می‌توانیم تشخیص دهیم همه آن‌ها تاریخ هستند، اما متأسفانه به صورتی متنی ذخیره شده‌اند و باید تبدیل شوند.

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

تابع DATEVALUE متن را به مقدار تاریخ تبدیل می‌کند در حالی که تابع VALUE متن یک مقدار را به عدد تبدیل می‌کند. تفاوت آن‌ها چندان بزرگ نیست. در تصویر فوق، یکی از مقادیر شامل اطلاعات زمانی نیز هست. این وضعیت موجب می‌شود که تغییرهای اندکی در تابع لازم باشد.

فرمول DATEVALUE زیر می‌تواند هر یک از مقادیر را به یک مقدار تاریخ تبدیل کند.

=DATEVALUE(A2)

توجه داشته باشید که زمان چگونه از ردیف 4 حذف شده است. این فرمول مستقیماً مقدار تاریخ را بازگشت می‌دهد. نتیجه کار باید به صورت تاریخ قالب‌بندی شود. فرمول زیر از تابع VALUE استفاده می‌کند.

=VALUE(A2)

این فرمول همان نتیجه مورد انتظار را در ردیف 4 ایجاد می‌کند و زمان نیز حضور دارد. نتایج را می‌توان به صورت تاریخ و زمان قالب‌بندی کرد و یا آن‌ها را به صورت تاریخ قالب‌بندی کرد تا مقدار زمان پنهان شود (گرچه حذف نمی‌شود).

اگر این مطلب برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

• مجموعه آموزش‌های نرم‌‌افزار اکسل Excel
• گنجینه آموزش های اکسل (Microsoft Excel)
• مجموعه آموزش‌های نرم‌افزارهای Microsoft Office (آفیس)
• توابع جدید اکسل 2۰1۹ — راهنمای کاربردی
• گرد کردن اعداد اعشاری در اکسل – آموزش گام به گام

==