وارد کردن تاریخ بدون جداکننده

Question

افراد مختلف داده ها را به روش های مختلف وارد می کنند. وقتی اطلاعاتی را در یک سلول وارد می کنید، اکسل سعی می کند بفهمد که چه نوع اطلاعاتی را وارد می کنید. اگر عددی مانند 08242020 را وارد کنید، اکسل فرض می کند که شما یک مقدار عددی را وارد می کنید و مطابق با آن رفتار می کند. اگر عددی که وارد می کنید در واقع یک تاریخ باشد، بدون هیچ جداکننده، چه؟ آیا اکسل می تواند بفهمد که چه چیزی را وارد می کنید؟

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

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

##"/"##"/"####

این فرمت شماره 08142020 را به عنوان 8/14/2020 نمایش می دهد. تنها مشکل این است که فقط نمایش عدد را تغییر می دهد—اگر می خواهید از تاریخ به عنوان تاریخ واقعی اکسل استفاده کنید، نمی توانید این کار را انجام دهید زیرا مقدار را به چیزی تبدیل نکرده اید که اکسل آن را به عنوان تاریخ تشخیص دهد.

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

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

1. ستون تاریخ ها را انتخاب کنید.
2. مطمئن شوید که چیزی در ستون سمت راست تاریخ ها وجود ندارد.
3. Text to Columns را از تب Data روبان در گروه Data Tools انتخاب کنید. اکسل جادوگر تبدیل متن به ستون را نمایش می دهد. (شکل 1 را ببینید.)
4. گزینه Fixed Width را انتخاب کرده و روی Next کلیک کنید.
5. دوباره روی Next کلیک کنید.
6. در قسمت Column Data Format، تاریخ را انتخاب کنید.
7. محدوده را در کادر Destination انتخاب کنید، سپس در کاربرگ روی سلول درست در سمت راست اولین مقداری که در مرحله 1 انتخاب کرده اید کلیک کنید.
8. بر روی Finish کلیک کنید.

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

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

=DATE(RIGHT(A1,4),LEFT(A1,IF(LEN(A1) = 8,2,1)),LEFT(RIGHT(A1,6),2))

این فرمول فرض می کند که تاریخ وارد شده (تاریخ بدون جداکننده) در سلول A1 است. این فرمول با تاریخ های هفت یا هشت رقمی کار می کند.

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

Function DateTime(dblDateTime As Double, _
  Optional bAmerican As Boolean = True)

 Converts Date and time "number" without
 delimiters into an Excel serial number (which
 can then be formatted with the Excel
 date/time formats)

 If optional argument is TRUE (or missing),
 function assumes value is of form:
    [m]mddyyyy.hhmm  (leading "0" not required)

 If optional argument is FALSE, function
 assumes value is of form:
    [d]dmmyyyy.hhmm  (leading "0" not required)

    Dim iYear As Integer
    Dim iMonth As Integer
    Dim iDay As Integer
    Dim iHour As Integer
    Dim iMin As Integer

    iYear = Int((dblDateTime / 10000 - _
      Int(dblDateTime / 10000)) * 10000)
    iDay = Int((dblDateTime / 1000000 - _
      Int(dblDateTime / 1000000)) * 100)
    iMonth = Int((dblDateTime / 1000000))
    iHour = Int((dblDateTime - Int(dblDateTime)) * 100)
    iMin = Int((dblDateTime * 100 - _
      Int(dblDateTime * 100)) * 100 + 0.5)

    If bAmerican Then
        DateTime = DateSerial(iYear, iMonth, iDay)
    Else
        DateTime = DateSerial(iYear, iDay, iMonth)
    End If

    DateTime = DateTime + (iHour + iMin / 60) / 24
End Function

این تابع ماکرو فرض می کند که داده های ارسال شده به آن یک مقدار عددی است، همانطور که معمولاً هنگام وارد کردن تاریخ ها بدون جداکننده اتفاق می افتد. (به منطق این موضوع در ابتدای نکته مراجعه کنید.)

همانطور که می توانید بگویید، تعدادی راه حل وجود دارد، اما هیچ یک از آنها به سادگی وارد کردن جداکننده ها هنگام وارد کردن تاریخ ها نیست. اگر آموزش دادن به خود یا افراد ورودی داده برای انجام این کار دشوار است، ممکن است برخی از قوانین اعتبارسنجی داده را برای سلول های ورودی تنظیم کنید. این قوانین می توانند بررسی کنند که اطلاعات را با استفاده از قالب خاصی (مانند تاریخ با جداکننده ها) وارد می کنید و اگر وارد نمی شوید، شما را متوقف کنند. (نحوه ایجاد قوانین اعتبارسنجی داده ها در سایر شماره های ExcelTips پوشش داده شده است. )

برای راه های بیشتر برای وارد کردن تاریخ ها و زمان ها بدون جداکننده (با استفاده از ماکرو)، این صفحه مفید را در سایت چیپ پیرسون بررسی کنید:

http://www.cpearson.com/Excel/DateTimeEntry.htm