تبدیل زمان و عدد اعشاری
فارسی کردن اعداد در اکسل
2018/03/04
ابزار Relationship در گزارش
2018/03/15تبدیل ساعت به عدد (زمان به عدد) و بالعکس در اکسل
یکی از پرکاربردترین سوالاتی که برای کابران اکسل پیش می‌آید نحوه محاسبه و تبدیل ساعت به عدد و نیز تبدیل عدد به ساعت می‌باشد که اکثرا برای راحتی در محاسبات علی‌الخصوص محاسبات حقوق و دستمزد برای افرادی که در رشته‌های حسابداری و مرتبط با مباحث مالی فعالیت دارند استفاده می‌گردد.
اصل ۱: ذکر این مورد در ابتدای این پست بسیار حائز اهمیت می‌باشد که نحوه صحیح وارد کردن ساعت در اکسل استفاده از ":" است و نوشتن ساعت به عنوان مثال به شکل 40.20 یا 20/40 اشتباه بوده و صحیح آن 20:40 می‌باشد.
اصل ۲: عدد ۱ در اکسل معادل یک روز یا ۲۴ ساعت می‌باشد.
تبدیل ساعت به عدد:
اگر برای مثال قصد داشته باشید ساعت 20:40 را به عدد متناظر با آن تبدیل کنید از روش زیر استفاده کنید:
دقت داشته باشید که ساعت باید به صورت 20:40 دقیقا نوشته شود به عبارتی دیگر فرمت سلول می‌بایست Time باشد.
روش اول:
فرض میکنیم ساعت مورد نظر خود را در سلول A1 نوشته باشید، در سلولی دیگر فرمول ذیل را بنویسید:
=HOUR(A1)+MINUTE(A1)/60+SECOND(A1)/3600
اگر بخواهید مستقیما بدون استفاده از سلول کمکی محاسبات فوق را انجام دهید رعایت دو نکته الزامی می‌باشد:
نکته ۱: وارد کردن مقدار ساعت بین دو علامت "
نکته ۲: رعایت نوشتن ساختار ساعت مطابق اصل ۱
=HOUR("20:40:15")+MINUTE("20:40:15")/60+SECOND("20:40:15")/3600
روش دوم:
در صورتی که زمان مورد نظر بیشتر از ۲۴ ساعت باشد روش ۱ پاسخگوی نیاز شما نبوده و می‌بایست از فرمول ذیل استفاده کرد:
=TEXT(A1,"[h]")+MINUTE(A1)/60+SECOND(A1)/3600
نکته ۳: فرمول فوق برای زمانهای کمتر از ۲۴ ساعت نیز پاسخ صحیح خواهد داد و می‌تواند جایگزین روش اول باشد.
تبدیل عدد به ساعت:
حالت ۱:
بالعکس روش بالا یعنی تبدیل عدد اعشاری به ساعت به این صورت می‌باشد که اگر عدد مورد نظر را در سلول A1 نوشته باشیم، فرمول تبدیل آن به ساعت به شکل ذیل می‌باشد:
=A1/24
علاوه بر فرمول ساده فوق می‌توان از تابع حرفه‌ای CONVERT نیز به روش ذیل استفاده کرد:
=CONVERT(A1, "hr", "day")
حال سلول یا ستون مورد نظر را انتخاب کرده و کلیک راست کنید و گزینه Format Cell رو اننتخاب کنید (کلید میانبر Ctrl+1) ، سپس از تب Number گزینه Custom را انتخاب و فرمت را به شکل ذیل مطابق تصویر تغییر دهید:
[h]:mm:ss
شکل ۲: نمونه تبدیل عدد به ساعت پس از اعمال فرمت سل
نکته ۴: علت تقسیم عدد اعشاری بر ۲۴ مطابق اصل ۲ گفته شده می‌باشد.
حالت ۲:
یکی از اشتباهات رایج در بین کاربران مبتدی اکسل عدم رعایت اصل ۱ و وارد نمودن ساعت با علامت‌های ممیز (.) ، اسلش (/) و ... می‌باشد. جهت اصلاح این مشکل و تبدیل آن به ساختار استاندارد ساعت می‌بایست از فرمول ذیل استفاده کنید:
=TIMEVALUE(TEXT(SUBSTITUTE(A2,".","")*10^(4-LEN(SUBSTITUTE(A2,".",""))),"00\:00"))
فرمول فوق جهت تبدیل ممیز به ساعت استاندارد می‌باشد، اگر شما تبدیل اسلش یا موارد دیگر به حالت استاندارد را نیاز دارید در فرمول بالا هرجا ممیز استفاده شده است، اسلش یا موارد دیگر را جایگزین کنید.
فرمول فوق را نمی‌توان یک فرمول جامع در نظر گرفت اما در اکثر موارد پاسخگوی نیاز شما خواهد بود.
24 دیدگاه ها
سلام و روز بخیر مجموع تقریبا ۳۰ساعات در اکسل ساعتی مثل ۱۰۲:۵۰ شده است می خواهم آن را به معادل عددی ش با فرمول در خانه ای از اکسل تبدیل کنم ممنون می شوم کمکم کنید ( هر ساعت در یک سلول نوشته شده و بعد در سلول دیگر مجموع آن محاسبه گردیده است )
سلام
چون زمان مورد نظر شما بیشتر از ۲۴ ساعت است از روش دوم تبدیل ساعت به عدد مطابق پست آموزشی فوق استفاده کنید
سلام و وقت بخیر
من از فرمولهای شما نتیجه نگرفتم نمیدونم چرا؟
مثلا برای اعدادی که صفر هستن بجای اینکه صفر بزنه خطا میزنه
دوم اینکه مثلا عدد 5.35 را که باید بشه 5:35 تبدیل به ساعت می کنه یعنی 18:20..به همون عدد تبدیل نمی کنه؟ و در جمع کلی هم که از مجموع چند ساعت هست خطا میزنه
بنظرت چرا؟
سلام دوست عزیز
عدد ۵.۳۵ به معنی پنج روز و ۳۵ صدم روز هست. برای همین عددی که شما مد نظرتون هست رو بهتون نمیده.
ممنون از راهنمایی مفیدتان
اگر زمانهای داده شده فقط دقیقه و ثانیه باشه و جمع اون ممکنه به ساعت برسه به چه صورت باید عمل کردکه در انتها بتونیم در یک عدد ریالی ضرب کرده و مبلغ به دست آورد؟
این حالتی که شما فرمودید با مواردی که در پست گفته شده تفاوتی نداره و با همین روش ها انجام میشه اما اگر باز هم سوالی داشتید لطفا در انجمن اکسل ایران مطرح بفرمایید
https://forum.exceliran.com/
سلام در اکسل وقتی یک عدد وارد میکنیم مثلا عدد ۱۲ بعد تبدیل میکنیم به تاریخ میشه ۱/۱۲/۱۹۰۰ که برمیگردونه به سال ساخت اکسل که سال ۱۹۰۰ بوده و اون ۱۲ تبدیل میشه به روز
حالا وقت ما یه ساعت میزنم مثلا ۶:۳۰ AM و تبدیلش میکنیم به عدد میشه ۰٫۲۷ میخاستم ببینم معیارش چیه که این عدد و به ما میده
سلام
در اکسل هر عدد نشان دهنده یک ساعت و تاریخ خاص است
مثلا عدد ۱ مشخص کننده تاریخ اولیه تعریف شده در اکسل ۱/۱/۱۹۰۰ می باشد و اگر شما تاریخ امروز را به عدد تعریف کنید ، عددی که نمایش می دهد نشاندهنده این است که N روز از روز اول تعریف شده گذشته است.
در خصوص ساعت هم به همین صورت ، عدد ۱ نشاندهنده یک روز کامل یعنی ۲۴ ساعت است و زمانی که ۶:۳۰ را می نویسید ۰٫۲۷ یعنی این میزان درصد از ۲۴ ساعت
SUM(TEXT(A1;”[h]”);MINUTE(A1)/60;SECOND(A1)/3600)=
از روش شما نتیجه ای نگرفتم ولی با کمی تغییر با این فرمول به نتیجه رسیدم. ممنون
hچیه؟
سلام دوست عزیز
h در فرمت سل به معنی ساعت است.
عالی بود دمت گرم
سلام
من یک مجموعه داده دارم که ستون زمان در آن بصورت مثلا لحظه 9 و 20 دقیقه و 15 ثانیه رو بصورت 92015 نمایش میده
چطور میتونم این عدد 92015 رو به فرمت درس زمان برگردونم یعنی: 9:20:15
ممنون
سلام دوست عزیز
فرض کنید عدد 92015 را در سلول A1 قرار داده اید.
از فرمول زیر برای تبدیل عدد به زمان میتونید استفاده کنید:
=VALUE(LEFT(A1)&":"&MID(A1,2,2)&":"&RIGHT(A1,2))همچنین میتوانید از این فرمول نیز استفاده کنید:
=TIMEVALUE(LEFT(A1)&":"&MID(A1,2,2)&":"&RIGHT(A1,2))فقط دقت بفرمایید ساعت باید یک رقمی و دقیقه و ثانیه باید حتما دو رقمی باشند.
با سلام و سپاس
فرمول =A1/24 رو برای تبدیل عدد به ساعت نوشتید، اگر تبدیل عدد به روز تیتر می‌زدید بهتر بود یا اینکه =A1/60 رو می‌نوشتید.
سلام دوست عزیز
خیر دوست عزیز. فرمول یاد شده عدد موجود در سلول را به ساعت تبدیل میکند نه به روز. علت استفاده از عدد 24 این است که عدد 1 در اکسل به معنی یک روز است نه یک ساعت. پس اگر ما نیاز به ساعت داشته باشیم باید بر عدد 24 تقسیم کنیم تا عدد وارد شده در سلول را تبدیل به ساعت کند.
موفق باشید
سلام
من میخواهم عددی را بر مجموع زمان تقسیم کنم. مثلا عدد 5000 را بر 80 ساعت تقسیم کنم.میخواهم 80 ساعت به عدد 80 تبدیل شود تا بتوانم در فرمول استفاده کنم.چه کنم؟
سلام دوست عزیز
کافیست شما ۸۰ ساعت را در عدد ۲۴ ضرب کنید. و بعد تقسیم مورد نظر را اعمال فرمایید.
سلام من میخواهم یک ساعت رابه ثانیه تبدیل کنم باید از چه فرمولی در اکسل استفاده کنم برای تولید قطعات است چون سیکل کاری با ثانیه محاسبه میشودمثلا 9:25:10را
قبلا از همکاری شما متشکرم
سلام دوست عزیز
کافیه فرمت سلول رو به [ss] تغییر بدین. متوجه میشوید ۹ ساعت و ۲۵ دقیقه و ۱۰ ثانیه تبدیل به ۳۳۹۱۰ ثانیه شده است.
راه حل دیگر این است که سلولی که در آن ساعت درج شده است را در عدد ۸۶۴۰۰ بکنید که معادل تعداد ثانیه های یک روز است.
=HOUR(A1)/24+MINUTE(A1)/60+SECOND(A1)/3600
/24 فراموش شده لطفا تصحیح کنید
سلام دوست عزیز
چیزی فراموش نشده. فرمول کاملا درست است.
سپاس از توجه شما
ممنون از راهنمایی‌های شما.
عالی, درود بر شما
با عرض سلام و خسته نباشید
بسیار عالی و کاربردی. سپاس از شما