تبدیل ساعت به عدد در اکسل – توضیح سریع ترین و ساده ترین روش

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

۱. تبدیل زمان به عدد اعشاری با تغییر قالب سلول

تبدیل یک داده زمانی به عدد اعشاری می‌تواند به راحتی با تغییر فرمت سلول به «General» انجام شود. در فرمت اعشاری عدد یک معادل ۲۴ ساعت است و سایر ساعات روز به صورت عددی اعشاری کوچکتر از یک نمایش داده می‌شود. انجام محاسبات با این تعداد اعشاری راحت‌تر انجام می‌شود. برای تبدیل ساعت به عدد در اکسل با استفاده از تغییر فرمت سلول طبق مراحل زیر عمل کنید:

فیلم آموزش قالب بندی در اکسل ۲۰۱۹ (رایگان) در فرادرس

کلیک کنید

1. سلول مد نظر را که شامل ساعت است انتخاب کنید.
2. در سربرگ «Home» بخش «Number» منوی کشویی را باز کنید و General را انتخاب کنید.
3. با تغییر فرمت سلول مشاهده خواهید کرد که ساعت تبدیل به یک عدد اعشاری می‌شود.

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

۲. عملیات حسابی برای تبدیل ساعت به عدد در اکسل

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

• با ضرب داده زمانی در ۲۴، تعداد ساعات موجود در داده، به صورت یک عدد اعشاری استخراج می‌شود.
• با ضرب داده زمانی در ۱۴۴۰ تعداد دقایق موجود در داده، به صورت یک عدد استخراج می‌شود. ۱۴۰۴ در واقع تعداد دقایق در یک روز و حاصل ضرب ۲۴ در ۶۰ است.
• در نهایت با ضرب داده زمانی در ۸۶۴۰۰ تعداد ثانیه‌های موجود استخراج می‌شود. ۸۶۴۰۰ نیز تعداد ثانیه‌های یک روز است که از ضرب ۲۴ در ۶۰ در ۶۰ حاصل شده است.

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

استخراج تعداد ساعات

فرض کنید قصد داریم تعداد ساعت موجود در داده‌های زمانی ستون A را استخراج کنیم برای این کار نیاز است محتوای هر سلول را طبق فرمول زیر در ۲۴ ضرب کنیم:

=A2*24

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

=INT(A2*24)

استخراج تعداد دقایق

در مثال مشابه قصد داریم تعداد دقایق موجود در داده زمانی را به صورت داده‌ای جداگانه داشته باشیم برای این کار می‌توانیم به سادگی داده‌های موجود در ستون A را در ۱۴۴۰ ضرب کنیم:

=A2*1440

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

=INT(A2*1440)

استخراج تعداد ثانیه‌ها

به طور مشابه در مثال زیر می‌توانیم با ضرب داده‌های زمانی ستون A در ۸۶۴۰۰ تعداد ثانیه‌ها را طبق فرمول زیر محاسبه کنیم:

=A2*86400

یادگیری ترفندهای محاسباتی کاربردی اکسل

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

• فیلم آموزش ترفند‌های کاربردی اکسل فرادرس
• فیلم آموزش رایگان اکسل در ۱۸۰ دقیقه فرادرس
• آموزش رایگان ترفندهای اکسل برای ناشنوایان فرادرس

اگر مایلید تمامی فیلم‌های آموزشی اکسل فرادرس را یکجا مشاهده کنید، روی لینک زیر کلیک کنید:

• مجموعه فیلم‌های آموزشی اکسل از مقدماتی تا پیشرفته فرادرس

۳. استفاده از تابع Convert برای تبدیل زمان به عدد

تابع Convert نیز می‌تواند عملیاتی مشابه را روی زمان انجام دهد و تعداد ثانیه‌ها دقیقه‌ها و ثانیه‌ها را از یک داده زمانی استخراج کند. سینتکس تابع Convert به صورت زیر است:

CONVERT(number,from_unit,to_unit)

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

در مثال زیر با استفاده از این فرمول تعداد ساعات موجود در داده زمانی ستون A را استخراج کرده‌ایم:

=CONVERT(A2, "day", "hr")

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

=CONVERT(A2, "day", "mn")

در نهایت با استفاده از فرمول زیر می‌توان تعداد ثانیه‌های موجود در داده زمانی را استخراج کرد:

=CONVERT(A2, "day", "sec")

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

با استفاده از توابع زمانی، داده را به ساعت دقیقه و ثانیه تجزیه می‌کنیم. ابتدا به معرفی این سه تابع می‌پردازیم:

• HOUR(serial_number)  : تابع HOUR ساعت موجود در یک داده زمانی را به عنوان خروجی برمی‌گرداند. خروجی این تابع عددی از 0 (نشان دهنده ۱۲ نیمه شب) تا 23 (نشان دهنده ۱۱ شب) است. تنها آرگومان این تابع، داده‌ای از جنس زمان است که می‌تواند در فرمت‌های زیر باشد:
  • داده متنی (Text String) داخل دو "  ، مثل « "6:32 PM"  »
  • عددی اعشاری مثل 0.78125 که نشان دهنده ساعت 6:45 عصر است. در این فرمت ۲۴ ساعت شبانه‌روز معادل «1» و سایر ساعات کسری از یک در نمایش عدد اعشاری هستند.
  • خروجی سایر توابع مثل TIMEVALUE("6:45 PM")
• MINUTE(serial_number)  : تابع MINUTE، دقیقه موجود در یک داده زمانی را به عنوان خروجی برمی‌گرداند. خروجی می‌تواند عددی از 0 تا 59 باشد. آرگومان این تابع مشابه تابع HOUR مقدار می‌پذیرد. به عنوان مثال اگر روی « "6:32 PM"  » تابع MINUTE را اعمال کنیم، عدد «32» برگردانده می‌شود.
• SECOND(serial_number)  : تابع SECOND، ثانیه موجود در یک داده زمانی را به عنوان خروجی برمی‌گرداند. خروجی می‌تواند عددی از 0 تا 59 باشد. آرگومان این تابع مشابه تابع HOUR مقدار می‌پذیرد. به عنوان مثال اگر روی « "6:32:22 PM"  » تابع SECOND را اعمال کنیم، عدد «22» برگردانده می‌شود.

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

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

فرمول زیر نیز با ترکیب توابع زمانی تعداد دقایق موجود در داده را استخراج می‌کند:

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

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

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

جداسازی ساعت، دقیقه و ثانیه

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

• با استفاده از تابع INT تعداد روزهای موجود در داده زمانی را استخراج می‌کنیم.
• با استفاده از توابع زمانی، ساعت، دقیقه و ثانیه را از داده استخراج می‌کنیم.
• مقادیر استخراج شده را در کنار هم در یک سلول با توضیح نمایش می‌دهیم.

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

=B4-$B$1

می‌توانید زمان حال را با تابع ()NOW نیز نشان دهید.

=B4-NOW()

حال اختلاف زمانی به دست آمده را با استفاده از فرمول زیر به روز، ساعت، دقیقه و ثانیه تجزیه می‌کنیم:

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

همانطور که مشاهده می‌کنید مقدار صفر نیز به عنوان خروجی در بعضی از سلول‌ها نوشته شده است. برای مثال در سلول D6 صفر روز تا شروع رویداد باقی مانده است. می‌توانیم با استفاده از تابع شرطی IF به سادگی مقادیر صفر را از نتیجه حذف کنیم:

=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","")

تبدیل ساعت به داده متنی

برای بسیاری از محاسبات و تحلیل‌ها نیاز است از رشته متنی برای تعریف داده استفاده کنیم. فرض کنید حجمی از داده زمانی دارید، اما نیاز دارید این داده‌ها را به فرمت متنی تبدیل کنید. اگر فرمت این سلول‌ها را به سادگی از زمان به متن تغییر دهید داده زمانی تبدیل به عددی اعشاری می‌شود. در واقع برای تعریف داده زمانی به عنوان داده متنی نیاز است از تابع TEXT استفاده کنید، برای مثال تصویر زیر را در نظر بگیرید. در این جدول از داده‌های زمانی محتوای ستون B، C وD هر چند نمایش زمانی دارند، اما به عنوان داده متنی برای اکسل تعریف شدند. آرگمان اول تابع TEXT زمان مد نظر و آرگمان دوم فرمت نمایش دلخواه شما را نشان می‌دهد.

فیلم‌های آموزشی مرتبط فرادرس

علاوه بر فیلم‌هایی که تا اینجا معرفی شدند، فیلم‌های آموزشی فرادرس زیر به شما کمک می‌کنند تا درک کامل‌تری از مفاهیم آموزشی پیدا کنید که در این مطلب آورده شده‌اند:

• فیلم آموزش رایگان تبدیل تاریخ شمسی به میلادی در اکسل فرادرس
• فیلم آموزش رایگان تبدیل تاریخ میلادی به شمسی در اکسل فرادرس
• فیلم آموزش رایگان قالب بندی در اکسل ۲۰۱۹ فرادرس
• فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل فرادرس

جمع‌بندی

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