تجزیه فرمت های غیر استاندارد تاریخ در اکسل

Question

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

اگر تاریخ ها در همان فرمت دنباله ای هستند که در تنظیمات منطقه ای خود استفاده می کنید، تبدیل کردن یک ضربه فوری است. به عنوان مثال، اگر تنظیمات منطقه ای شما از قالب تاریخ MDY (ماه به دنبال آن روز به دنبال سال) استفاده می کند، و تاریخی که وارد می کنید در همان قالب است، می توانید به سادگی سلول ها را انتخاب کرده و نقطه ها را با یک اسلش جایگزین کنید. هنگامی که اکسل 9.15.2020 را به 9/15/2020 تغییر می دهد، به طور خودکار نتیجه را به عنوان تاریخ تجزیه می کند.

اگر قالبی که وارد می‌کنید با تنظیمات منطقه‌ای شما مطابقت ندارد، باید تاریخ را در همان قالب تغییر دهید. به عنوان مثال، اگر تاریخی که وارد می‌کنید 09.10.20 (10 سپتامبر 2020) است، و سیستم شما این را به عنوان 9 اکتبر 2020 تفسیر می‌کند، ساده‌ترین راه این است که تاریخ را به اجزای جداگانه جدا کنید و سپس آن‌ها را برگردانید. با یکدیگر. این مراحل کلی را دنبال کنید:

     =DATE(C1,A1,B1)

1. سه ستون خالی در سمت راست ستون تاریخ درج کنید.
2. سلول های حاوی تاریخ های غیر استاندارد را انتخاب کنید.
3. با استفاده از ابزار Text to Columns (موجود در گروه Data Tools در زبانه Data روبان) داده های محدود شده را انتخاب کنید و از نقطه به عنوان جداکننده استفاده کنید. پس از اتمام کار جادوگر، سه ستون شامل ماه، روز و سال خواهید داشت.
4. در ستون خالی باقیمانده، فرمولی مانند زیر را وارد کنید:
5. فرمول را در سلول های دیگر کنار تاریخ ها کپی کنید.
6. سلول های حاوی فرمول هایی را که ایجاد کرده اید انتخاب کنید، سپس Ctrl+C را فشار دهید .
7. از Paste Special برای تبدیل فرمول ها به نتایج استفاده کنید. (هنگام استفاده از Paste Special گزینه Values ​​را انتخاب کنید.)
8. سه ستون حاوی تاریخ های جدا شده را حذف کنید و ستونی را که حاوی تاریخ های نهایی است نگه دارید.

راه حل دیگر این است که به سادگی از یک ماکرو برای انجام تبدیل استفاده کنید. زیر یک تابع تعریف شده توسط کاربر است که تاریخ غیر استاندارد را می گیرد و آن را به یک مقدار تاریخ با فرمت مناسب تبدیل می کند. ماکرو همچنین موقعیت ماه و روز را تغییر می دهد، همانطور که در تکنیک Text to Columns انجام می شود.

Public Function Convert_Date(A As String) As Date
    Dim K As Long
    Dim K1 As Long
    Dim K2 As Long

    K = Len(A)
    K1 = InStr(1, A, ".")
    K2 = InStr(K1 + 1, A, ".")
    Convert_Date = DateSerial(Val(Mid(A, K2 + 1, _
      K - K2 + 1)), Val(Mid(A, K1 + 1, K2 - K1)), _
      Val(Mid(A, 1, K1 - 1)))
End Function