تبدیل مقادیر عددی به Times

Question

سام کاربرگ های زیادی دارد که حاوی زمان هستند. مشکل اینجاست که زمان ها به جای فرمت «13:00» در قالب «1300» است. بنابراین، اکسل به جای اینکه آنها را به عنوان زمان تشخیص دهد، آنها را به عنوان مقادیر عددی منظم می بیند. سام می خواهد آنها به مقادیر زمانی واقعی تبدیل شوند.

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

=TIME(LEFT(A1,2),RIGHT(A1,2),)

این فرمول فرض می کند که زمان در سلول A1 همیشه شامل چهار رقم خواهد بود. اگر اینطور نیست (به عنوان مثال، ممکن است به جای 0427 427 باشد)، پس فرمول باید کمی اصلاح شود:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)

این فرمول اساساً سمت چپ ترین رقم (یا ارقام) را می کشد و از آنها برای آرگومان ساعت تابع TIME استفاده می کند و سپس از دو رقم سمت راست برای آرگومان دقیقه استفاده می کند. TIME یک مقدار زمان واقعی را برمی گرداند که به این صورت در سلول قالب بندی شده است.

یک رویکرد فرمولی مشابه را می توان با استفاده از تابع TIMEVALUE اتخاذ کرد:

=TIMEVALUE(REPLACE(A1,LEN(A1)-1,0,":"))

این فرمول از REPLACE برای درج دو نقطه در محل مناسب استفاده می کند و سپس TIMEVALUE نتیجه را به مقدار زمانی تبدیل می کند. شما باید سلول به دست آمده را طوری فرمت کنید که زمان را همانطور که می خواهید نمایش دهد.

یکی دیگر از تغییرات در رویکرد فرمولی استفاده از تابع TEXT به این صورت است:

=--TEXT(A1,"00:00")

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

روش دیگر این است که به سادگی محاسبه زمان اصلی را انجام دهید تا آن را به مقدار زمانی مورد استفاده اکسل تبدیل کنید. وقتی متوجه شدید که مقادیر زمانی چیزی جز بخشی جزئی از روز نیستند، این کار آسان است. بنابراین، مقدار زمانی عددی بین 0 و 1 است که از تقسیم ساعت بر 24 (ساعت در روز) و دقیقه بر 1440 (دقیقه در روز) به دست می آید. در اینجا فرمولی وجود دارد که این کار را انجام می دهد:

=INT(A1/100)/24+MOD(A1,100)/1440

این قسمت ساعت مقدار اصلی را تعیین می کند که سپس بر 24 تقسیم می شود. قسمت دقیقه (قسمت باقی مانده از مقدار اصلی) سپس بر 1440 تقسیم می شود و به قسمت اول اضافه می شود. سپس می توانید نتیجه را به صورت زمان فرمت کنید و کاملاً کار می کند.

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

Sub NumberToTime()
    Dim rCell As Range
    Dim iHours As Integer
    Dim iMins As Integer

    For Each rCell In Selection
        If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then
            iHours = rCell.Value  100
            iMins = rCell.Value Mod 100
            rCell.Value = (iHours + iMins / 60) / 24
            rCell.NumberFormat = "h:mm AM/PM"
        End If
    Next
End Sub

ماکرو از یک تقسیم عدد صحیح برای تعیین تعداد ساعت ها (iHours) استفاده می کند و بقیه را در iMins قرار می دهد. سپس این مقدار در یک مقدار زمان تنظیم می شود و دوباره در سلول قرار می گیرد، که سپس به عنوان زمان قالب بندی می شود. در صورت تمایل می توانید قالب سلول را به هر یک از قالب های زمانی دیگر که توسط اکسل پشتیبانی می شود تغییر دهید.