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

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

به طور کلی، دو راه برای تبدیل زمان به عدد اعشاری در اکسل وجود دارد: 1) با استفاده از تغییر فرمت سلول و 2) با استفاده از محسبات یا توابع زمان اکسل مانند HOUR، MINUTE و SECOND. در ادامه این آموزش توضیحات مفصلی در مورد روش اول بیان خواهد شد. همچنین فرمول ها و تکنیک هایی همراه با مثال خواهید آموخت.

• نحوه تبدیل زمان به عدد اعشاری در اکسل
  • تبدیل زمان به ساعت در اکسل
  • تبدیل زمان به دقیقه در اکسل
  • تبدیل زمان به ثانیه در اکسل
• نحوه جدا کردن داده ها و زمان در ستون های جداگانه
• هجی کردن (Spell) زمان (XX days, XX hours, XX minutes, XX seconds )
• تبدیل متن به زمان با استفاده از تابع TIMEVALUE
• تبدیل زمان به متن
• تبدیل فرمت به فرمت زمان

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

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

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

• برای تبدیل زمان به تعداد ساعت، زمان را در 24 ضرب کنید. (24 تعداد ساعت در یک روز است.)
• برای تبدیل زمان به تعداد دقیقه، زمان را در 1440 ضرب کنید. (1440 تعداد دقیقه در یک روز است.)
• برای تبدیل زمان به تعداد ثانیه، زمان را در 86400  ضرب کنید. (86400 تعداد ثانیه در یک روز است.)

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

تبدیل زمان به ساعت در اکسل

در این بخش سه فرمول مختلف برای تبدیل ساعت از قالب زمان استاندارد (hh:mm:ss) به یک عدد اعشاری نشان داده شده است.

فرمول 1: محاسبات

همانطور که میدانید سریع ترین راه برای تبدیل زمان به تعداد ساعت در اکسل ضرب کردن آن زمان در 24 است. (24 تعداد ساعت در یک روز است.)

فرض کنید در سلول A2 مقدار زمان نوشته شده است.

=A2*24

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

=INT(A2*24)

فرمول 2: تابع Convert

راه دیگر برای تبدیل زمان به ساعت، استفاده از فرمول Convert زیر است:

=CONVERT(A2, "روز", "ساعت")

فرمول 3: تابع Hour، Minute و Second

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

=Hour(A2)+ Minute(A2)/60+ Second(A2)/3600

تبدیل زمان به دقیقه در اکسل

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

فرمول 1: محاسبات

برای تبدیل زمان به دقیقه، زمان را در عدد 1440 ضرب کنید. (1440 تعداد دقیقه در یک روز است. 1440=60*24)

=A2*1440

برای تبدیل به تعداد صحیح دقیقه و رهایی از اعشار، از تابع INT مشابه مثال زیر استفاده کنید:

=INT(A2*1440)

شما میتوانید نتیجه را در تصویر زیر ببینید:

فرمول 3: توابع Hour، Minute و Second

روش دیگر برای بدست آوردن تعداد دقیقه، ضرب ساعت در 60 و تقسیم ثانیه ها بر 60 است:

=HOUR(A2)*60 + MINUTE(A2) + SECOND(A2)/60

تبدیل زمان به ثانیه در اکسل

فرمول 1: محاسبات

زمان را در 86400  ضرب کنید. (86400 تعداد ثانیه در یک روز است. 24 ساعت ضربدر 60 دقیقه ضربدر 60 ثانیه)

=A2*86400

فرمول 2: تابع Convert

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

=CONVERT(A2, “day”, “sec”)

فرمول 3: توابع Hour، Minute و Second

مشابه مثال های قبلی از توابع Hour،   Minute و Second استفاده کنید. (مطمئنم شما در این مرحله نیازی به توضیح در مورد منطق فرمول زیر ندارید.)

=HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2)

نکات:

1. اگر هر کدام از فرمول های بالا مقادیر زمانی را برگرداند، فرمت سلول را به General تغییر دهید تا آن را به صورت یک عدد نمایش دهد.
2. برای تبدیل زمان به عدد اعشاری که در سیستم داخلی اکسل به صورت زمان نشان داده میشود؛ فرمت General را اعمال کنید. به این ترتیب به عنوان مثال زمان 23:59:59  به 0.99999 ، زمان 06:00AM به 0.25  و 12:00PM به 0.5  تبدیل خواهد شد. اگر قسمت صحیح عدد تبدیل شده، بیشتر از صفر باشد، به این معناست که سلول شما دارای هر دو مقادیر تاریخ و زمان است.

نحوه جدا کردن داده ها و زمان در اکسل

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

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

با فرض اینکه تاریخ و زمان اصلی شما در ستون A باشد، فرمول زیر تاریخ و زمان را جدا خواهد کرد:

=INT(A2)

برای استخراج قسمت زمانی، فرمول بالا را از مقدار تاریخ و زمان اصلی کم کنید:

=A2-B2

ستون A شامل تاریخ و زمان اصلی است و ستون B شامل تاریخ برگردانده شده با استفاده از تابع INT است.

در فرمول بالا برای استخراج مقادیر زمانی لازم است ابتدا مقادیر تاریخ را جدا کنید و سپس مقادیر زمانی را با استفاده از فرمول بالا محاسبه کنید. اگر میخواهید تنها مقادیر زمانی را داشته باشید و مستقیم این استخراج را انجام دهید. (برای مثال ممکن است در آینده بخواهید ستون تاریخ را حذف کنید.)

از فرمول MOD که در زیر آمده است استفاده کنید:

=MOD(A2,1)

نکته:

اگر مقادیر تاریخ و زمان جدا شده به درستی نمایش داده نمیشوند؛ به ترتیب فرمت ستون های جدید را به فرمت تاریخ و زمان تغییر دهید.

به این ترتیب تاریخ و زمان را در اکسل جدا میکنید. اگر میخواهید ساعت، دقیقه و ثانیه را در ستون های جداگانه قرار دهید، از توابع Hour، Minute و Second استفاده کنید (به همان ترتیبی که در نحوه دریافت ساعت، دقیقه و ثانیه از یک جدول زمانی در مثال های قبلی نشان داده شد.)

به عنوان مثال در تصویر بالا میخواهید به ترتیب ساعت، دقیقه و ثانیه  (سلول A2) را جدا کنید:

=Hour(mod(A2,1))

=Minute(mod(A2,1))

=Second(mod(A2,1))

نحوه هجی کردن (Spell) زمان در اکسل

گاهی شاید لازم باشد زمان را به فرمتی شبیه به “#روز، #ساعت، #دقیقه و #ثانیه” تبدیل کنید.نکته خوب این است که شما در قبل تمام عناصر تشکیل دهنده فرمول را آموختید:

1. روزها را با استفاده از تابع INT استخراج کنید.
2. بخش زمان را با استفاده از توابع Hour، Minute و Second استخراج کنید. و
3. تمام قسمت ها را با استفاده از یک فرمول به هم وصل کنید.

در فهم اینکه چه فرمولی را برای کاربرگ خود بنویسید مشکل دارید؟ مثال زیر فهم این کارها را آسان میکند.

فرض کنید در سلول B4 تاریخ وقایع آینده را دارید و در سلول B1  با استفاده از تابع NOW() زمان و تاریخ فعلی را استخراج کرده اید.

حال اجازه دهید، تایمر شمارش معکوسی بسازیم که نشان میدهد چند روز، ساعت، دقیقه و ثانیه تا هر رویداد باقی مانده است.

فرمول زیر فرمولی است که باید در سلول D4 نوشته شود:

=INT(C4) & " days, " & HOUR(C4) & " hours, " & MINUTE(C4) & " minutes and " & SECOND(C4) & " seconds"

اگر میخواهید از مقدار صفر (مانند مقدار صفر در سلول های D6 و D7 خلاص شوید.)؛ از فرمول زیر استفاده کنید:

=IF(INT(C4)>0, INT(C4)&" days, ","") & IF(HOUR(C4)>0, HOUR(C4) & " hours, ","") & IF(MINUTE(C4)>0, MINUTE(C4) & " minutes and ","") & IF(SECOND(C4)>0, SECOND(C4) & " seconds","")

به این ترتیب همه صفرها از بین رفتند!

نکته:

توجه کنید؛ هنگامی که هر یک از فرمول های بالا به یک عدد منفی اشاره داشته باشد؛ خطای #NUM! نشان داده میشود. این مورد ممکن است زمانی اتفاق بیفتد که زمان بیشتری را از زمان کمتری تفریق کنید.

 

تبدیل متن به زمان در اکسل

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

تابع Timevalue اکسل فقط یک آرگومان دارد:

 TIMEVALUE(time_text)

Time-text یک رشته متنی با هر فرمت زمانی است که اکسل میتواند آن را شناسایی کند. برای مثال

=TIMEVALUE("6:20 PM")

=TIMEVALUE("6-Jan-2015 6:20 PM")

=TIMEVALUE(A2)

سلول A2 شامل یک رشته متنی است.

همانطور که میبینید، فرمول هایی که شامل سلول مرجع هستند (فرمولی که در سلول B6 نوشته شده است.) با فرمولی که رشته متن مربوطه در آن نوشته شده است (فرمولی که در سلول B8 نوشته شده است.) نتایج یکسانی را برگردانده اند. همچنین توجه کنید که رشته های زمان (مقادیر زمان) در سلول های A2 و A6 در سمت چپ سلول قرار گرفته شده اند؛ درحالیکه که مقادیر زمانی تبدیل شده در ستون D در سمت راست سلول قرار گرفته شده اند.

تبدیل زمان به متن در اکسل

فرض کنید شما یک فایل اکسل پر از فرمت زمان شبیه به «8:30:00 AM» دارید و میخواهید آن ها را به فرمت متن تبدیل کنید. تبدیل فرمت سلول به متن به درستی کار نمیکند زیرا این کار مقادیر زمانی شما را به صورت نمایش عددی تغییر میدهد. برای مثال 8:30:00AM  به عدد اعشاری 0.354166666666667  تبدیل خواهد شد.

بنابراین، شما چگونه میتوانید سلول های خود را به فرمت متن تبدیل کنید به طوری که سلول های شما همچنان شامل زمان باشند؟ پاسخ این است که از تابع TEXT استفاده کنید که یک مقدار عددی را به متن تبدیل کند؛ با همان فرمتی که شما مشخص کرده اید. برای مثال:

=TEXT($A2,"h:mm:ss")

تصویر زیر سایر فرمت های ممکن را نشان میدهد:

نحوه تبدیل اعداد به فرمت زمان در اکسل

اگر شما لیستی از اعداد مانند 1، 2، 3.5 داشتید و میخواستید آن ها را به فرمت زمان تبدیل کنید؛ برای مثال 1:00:00 ، 2:00:00 یا 3:30AM. گام های زیر را طی کنید:

1. اعداد را بر 24 تقسیم کنید. (یک روز 24 ساعت است.) فرمول میتوانید به سادگی فرمول =A2/24 باشد.
2. سلول(ها)ی شامل فرمول را انتخاب کنید، کلیک راست کنید و گزینه Format Cells را از منو انتخاب کنید یا کلید Ctrl+1 را بزنید.در هر صورت، پنجره Format Cells ظاهر خواهد شد، از صفحه سمت چپ گزینه Time را که زیر قسمت Category قرار گرفته است را انتخاب کنید، و از صفحه سمت راست فرمتی که میخواهید را از زیر قسمت Type انتخاب کنید.

برای امروز کافیه.

از اینکه این مطلب را خواندید از شما متشکریم؛ امیدواریم از خواندن این مطلب لذت برده باشید.

مطالب زیر نیز برایتان مفید خواهید بود : )

همه چیز در مورد فرمت سلول

جمع ساعت در اکسل بیش از 24 ساعت

 

 

=a1*24*1000000