تاریخ و زمان - تبدیل متن یا شماره به تاریخ در اکسل

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

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

آیا یک مقدار DATE یا TEXT است

دانستن اینکه آیا یک مقدار خاص یک تاریخ است یا متن با دیدن صفحه اکسل، غیرممکن است. شما باید آنها را بررسی کنید. ما می توانیم با استفاده از تابع ISTEXT این کار را انجام دهیم.

اگر مقدار متن باشد، این تابع TRUE را برمی گرداند، و در غیر اینصورت FALSE .

به مثال زیر نگاهی بیندازید. در ستون A، تاریخ وجود دارد، اما برخی از آنها به عنوان متن و سایر به عنوان تاریخ قالب بندی می شوند.

اگر Ctrl + ` را فشار دهید، متوجه خواهید شد که چگونه این مقادیر ذخیره می شوند.

همچنین می توانید با کلیک بر روی آن و نگاه کردن به Home > Number، بررسی کنید که آیا متن به عنوان تاریخ فرمت بندی شده است یا خیر.

شما باید در اینجا مراقب باشید. اگر قبل از تاریخ یک apostrophe (‘) واحد قرار دهید (یا هر مقدار یا فرمول دیگری)، Excel با استفاده آن به عنوان متن رفتار می کند، حتی اگر قالب تاریخ را در Home > Number نشان دهد.

این apostrophe فقط در نوار فرمول قابل مشاهده است و نه در یک سلول. تابع ISTEXT نیز مقدار TRUE را بازگردانده است.

نحوه تبدیل یک عدد به تاریخ

هیچ نوع قالب DATE در اکسل وجود ندارد. تاریخ در اکسل همان اعداد با قالب تاریخ هستند.

مثال زیر اعدادی که در ستون A است را به عنوان تاریخ در ستون B قالب بندی کرده است. با افزایش عدد از 1، تاریخ را یک روز افزایش می دهد و از تاریخ 1/1/1900 شروع می شود.

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

از تابع TEXT استفاده می کنیم

اگر می خواهید قالب تاریخ را تغییر داده و سپس آن را به متن تبدیل کنید، می توانید آن را با تابع TEXT انجام دهید.

این تابع یک فرمت خاص را به متن تبدیل می کند.

=TEXT(A2,"dd-mm-yyyy")

پنجره قالب سلول

اگر می خواهید بدون تبدیل تاریخ های موجود به متن، قالب آنها را تغییر دهید، می توانید با دسترسی به پنجره Format Cells این کار را انجام دهید.

سلولهایی را که می خواهید قالب آنها را تغییر دهید انتخاب کنید و Ctrl + 1 را فشار دهید.

در زیر دسته Date، می توانید یک کشور و فرمت مورد نظر خود را انتخاب کنید. مثلا d-m-yyyy را انتخاب کنید.

این نتیجه ای است که می گیریم.

تغییر متن به تاریخ

اگر یک تاریخ به شکل متن دارید، نمی توانید آن را به قالب تاریخ مختلف تغییر دهید. ابتدا باید آن را به یک عدد و سپس به تاریخ تبدیل کنید.

تابع DATEVALUE تنها یک آرگومان است، که یک تاریخ را در یک فرم از متن می گیرد.

سپس تابع این متن را به یک عدد تبدیل می کند. این شماره بعداً با فرمت تاریخ متفاوت قابل تبدیل به تاریخ است.

=DATEVALUE(A2)

اگر می خواهید یک تاریخ را به یک عدد تغییر دهید، می توانید به جای آن از تابع VALUE استفاده کنید.

شماره 8 رقمی را به تاریخ تبدیل کنید

وقتی فایلی را با تاریخ های نوشته شده به عنوان شماره 8 رقمی دریافت می کنید ، ابتدا باید قالب های تاریخ کشور (YMD, DMY, MDY) را بشناسید. اگر نمونه های زیادی دارید، تشخیص آن آسان است.

YMD	DMY	MDY
20160101	11012011	11112018
20090520	21102016	07032017
20191127	07012009	11212016

اگر فقط جدول را کپی و بچسبانید، این نتیجه را می گیرید.

سلولهای B4 و C3 صفرهای پیشرو ندارند. به این دلیل است که اکسل به طور پیش فرض آنها را حذف می کند. حتی اگر سلول ها را به عنوان متن کپی کنید ، صفر ها حذف می شوند. می توانید سلول ها را به صورت متن قالب بندی کنید و صفر ها را با دست اضافه کنید ، اما روش بهتری برای این کار وجود دارد.

قبل از کپی کردن سلول ها ، سلول های خالی را انتخاب کنید که در آن مقادیر کپی می شوند ، کلیک راست کرده و Format Cells را انتخاب کنید .

همچنین می توانید از میانبر صفحه کلید Ctrl + 1 استفاده کنید.

در پنجره طبقه بندی ، Text را انتخاب کنید.

بر روی OK کلیک کنید.

جدول را کپی کرده و با انتخاب گزینه Match destination formatting، آن را در کاربرگ اکسل قرار دهید.

پس از چسباندن این مقادیر ، فراموش نکنید که بقیه آنها را به فرمت General تبدیل کنید.

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

تابع MID

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

YMD	نتیجه YMD
= DATE (MID (A2،1،4) ، MID (A2،5،2) ، MID (A2،7،2))	1/1/2016
DMY	نتیجه DMY
= DATE (MID (B2،5،4) ، MID (B2،3،2) ، MID (B2،1،2))	1/11/2011
MDY	نتیجه MDY
= DATE (MID (C2،5،4) ، MID (C2،1،2) ، MID (C2،3،2))	11/11/2018

توابع LEFT ، MID و RIGHT

در روش دوم از دو تابع اضافی استفاده خواهیم کرد: LEFT و RIGHT . این فرمول قابل خواندن است زیرا این دو تابع ، به جای سه ، دو پارامتر را به خود اختصاص می دهند.

YMD	نتیجه YMD
= DATE (LEFT (A2،4) ، MID (A2،5،2) ، RIGHT (A2،2))	1/1/2016
DMY	نتیجه DMY
= DATE (RIGHT (B2،4) ، MID (B2،3،2) ، LEFT (B2،2))	1/11/2011
MDY	نتیجه MDY
= DATE (RIGHT (C2،4) ، LEFT (C2،2) ، MID (C2،3،2))	11/11/2018

وقتی از Text to Columns Wizard استفاده می کنیم ، معمولاً یک جدا کننده تعیین می کنیم. اما در اینجا ، ما هیچ چیزی نداریم. گزینه دیگری وجود دارد که می توانید برای جدا کردن مقادیر، به نام Fixed width که از آن استفاده کنید. ما می توانیم از این ویژگی استفاده کنیم زیرا عرض ثابت 8 رقمی داریم.

روی Next کلیک کنید.

دو خط را برای جدا کردن مقادیر ایجاد کنید.

در پایان کلیک کنید لازم نیست که به مرحله 3 برویم. داده های ما به نوع General تبدیل می شوند .

مثال ما به روز ، ماه و سال جدا خواهد شد.

شماره 6 رقمی را به روز تبدیل کنید

اگر لیستی از تاریخ هایی را تهیه کنید که از 6 عدد تشکیل شده باشد ، باید فرمول را اصلاح کنید. اگر می دانید یک سال بزرگتر از 2000 نیست ، می توانید از فرمول زیر برای DMY استفاده کنید.

=DATE(20&RIGHT(B2,2),MID(B2,3,2),LEFT(B2,2))

این نتیجه را به شما می دهد.

1/11/2011

اگر 20& را قبل از تابع RIGHT قرار ندهید ، سال به جای سال 2011 تبدیل به 1911 می شود.

بیایید تصور کنیم که شما سالهای قبل از 1980 ندارید. در این فرمول ، ما می خواهیم هر سال کوچکتر از 1980 تا 2000 و بزرگتر از 80 تا 1900 را تبدیل کنیم.

=DATE(IF(INT(RIGHT(A2,2))>=80,19&RIGHT(A2,2),20&RIGHT(A2,2)),MID(A2,3,2),LEFT(A2,2))

توضیح فرمول

فرمول سالی را تشکیل می دهد که از دو رقم به یک عدد صحیح تبدیل می شود. اگر می خواهید مقایسه انجام دهید ، این مهم را به عدد تبدیل کنید. در مرحله بعد ، بررسی می کند که عدد بزرگتر است یا مساوی 80. اگر TRUE را برگرداند، در ابتدا 19 اضافه می کند. اگر FALSE باشد ، 20 را اضافه می کند.

محدود کننده ها را تغییر دهید

یافتن و جایگزین کردن

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

به مثال زیر نگاهی بیندازید.

با تاریخ های انتخاب شده (A2: A11) از Ctrl + H استفاده کنید. این میانبر صفحه کلید پنجره Find and Replace را باز می کند. در اینجا می توانید یک کاراکتر را با کاراکتر دیگر جایگزین کنید.

روی Replace All کلیک کنید. جدول ما تغییر کرده و اکنون تاریخ به عنوان تاریخ قالب بندی شده است.

چی شد؟ برخی از مقادیر به تاریخ تبدیل شده اند و برخی دیگر متن باقی مانده اند. تاریخ قبلاً به قالب ایالات متحده (MDY) تبدیل شده است.

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

تنظیمات منطقه و زبان

در قسمت جستجو شروع “Control Panel” را تایپ کنید تا نماد کنترل پنل ظاهر شود.

در بخش Clock and Region، گزینه Change date, time, or number formats را انتخاب کنید. در پنجره Region، روی Additional Settings کلیک کنید. سپس، برگه Date را انتخاب کنید.

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

قالب Short date را اصلاح کنید.