تبدیل فرمت تاریخ پشتیبانی نشده در اکسل

Question

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

با این حال، وقتی صحبت از تاریخ و زمان به میان می آید، همه برنامه ها به گونه ای صحبت نمی کنند که اکسل بتواند آن را درک کند. به عنوان مثال، اگر برنامه دیگر شما تاریخ را در قالب "Mon Jan 13 14:33:03 2011" ذخیره می کند، اکسل نمی تواند تاریخ را تجزیه کند و باید تبدیل را به روش دیگری انجام دهید.

خوشبختانه، اکثر برنامه ها تاریخ و زمان خود را در قالبی تولید می کنند که از یک الگو پیروی می کند. به عنوان مثال، با فرض اینکه "Mon Jan 13 14:33:03 2001" فرمتی را نشان می دهد که همه تاریخ ها دنبال می شوند، می توانید با استفاده از یک فرمول ساده تبدیل را انجام دهید:

=DATEVALUE(MID(A1,9,2)&MID(A1,5,3)&RIGHT(A1,4)) + TIMEVALUE(MID(A1,12,8))

این فرمول فرض می کند که فرمت تاریخ/زمان خارجی در سلول A1 است. به سادگی نتیجه فرمول را با استفاده از یکی از فرمت های تاریخ/زمان اکسل فرمت کنید و مشکلی نخواهید داشت.

اگر ترجیح می دهید، می توانید از تابع Text to Columns برای شکستن قالب تاریخ/زمان خارجی به بخش های جدایی ناپذیر آن استفاده کنید:

شکل 1. جادوگر تبدیل متن به ستون.

1. مطمئن شوید که چهار ستون خالی در سمت راست تاریخ/زمان وجود دارد. اینجا جایی است که اکسل قسمت های مختلف تاریخ/زمان را قرار می دهد.
2. تمام سلول های حاوی تاریخ/زمان های خارجی را انتخاب کنید.
3. تب Data روبان را نمایش دهید.
4. روی ابزار Text to Columns در گروه Data Tools کلیک کنید. اکسل جادوگر تبدیل متن به ستون را شروع می کند. (شکل 1 را ببینید.)
5. مطمئن شوید که Delimited انتخاب شده است، سپس روی Next کلیک کنید. اکسل مرحله دوم ویزارد را نمایش می دهد.
6. مطمئن شوید که کادر Space انتخاب شده است.
7. روی Finish کلیک کنید.

تاریخ ها و زمان ها اکنون به پنج ستون جداگانه تقسیم می شوند. اکنون می توانید از یک فرمول برای قرار دادن یک تاریخ/زمان معتبر دوباره استفاده کنید. به عنوان مثال، با فرض اینکه نسخه انفجاری تاریخ/زمان در سلول های A1:E1 باشد، می توانید از موارد زیر استفاده کنید:

=(C1&B1&E1)+D2

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

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

Sub ConvDate()
    Dim c As Range
    
    For Each c In Selection.Cells
        c = DateValue(Mid(c, 5, 6) & ", " _
          & Mid(c, 21, 4)) + TimeValue(Mid(c, 12, 8))
        c.NumberFormat = "dd MMMM yyyy h:mm:ss"
    Next c
End Sub

ماکرو رشته متن را به تاریخ/زمان قابل قبولی تبدیل می کند (با استفاده از DateValue) و سپس سلول را برای نمایش ویژگی مقدار فرمت می کند.