هر آنچه در کار با ساعت در اکسل باید بدانید
محاسبات مربوط به زمان (جمع ساعت در اکسل)
تاریخ و زمان در اکسل یکی از مباحثی است که خیلی مورد سوال قرار میگیره به عنوان مثال جمع ساعت در اکسل . مثلا یک برنامه برای ثبت ساعت ورود و خروج افراد تنظیم کردیم و میخواهیم از طریق اون محاسبات مربوط به حقوق، اضافه کاری، کسری کار و ….. محاسبه کنیم. پس باید بدونیم زمان در اکسل چه مفهومی داره و محاسباتش به چه صورته.
در این آموزش هر نکته ای راجع به زمان وجود داره رو با هم مرور خواهیم کرد.
مطابق شکل ۱ در دو سلول، زمان یکسان رو وارد کنید. فرمت یک سلول رو به Number تغییر بدید. این عدد به چه معنی است؟
شکل ۱- مفهوم مقدار زمان
همونطور که در شکل ۱ میبینید، ۱۲:۵۳ بعد از ظهر، معادل ۰.۵۴ روز هست. بعبارتی ۲۴ ساعت کامل در اکسل معادل عدد ۱ هست. در واقع محاسبات ساعت در اکسل، نسبت به روز سنجیده میشه. پس اگر میخوایم محاسباتی که داریم انجام میدیم، بر اساس ساعت باشه، مثلا میخوایم ساعت اضافه کاری رو در مبلغ هر ساعت ضرب کنیم. باید حواسمون باشه که اکسل اون جمع ساعت رو به روز تبدیل میکنه و بعد در مبلغ هر ساعت ضرب میکنه. به نکات زیر دقت کنید:
همونطور که در شکل ۲ می بینید، حاصل جمع چند ساعت اضافه کاری بدرستی نمایش داده نمیشه. با یک حساب سرانگشتی می بینیم که مجموع ساعت ها بیش از ۵۰ ساعت هست. برای اینکه نمایش جمع رو اصلاح کنیم، بر روی سلول B8 کلیک راست کرده و در Format Cell/ Custom کد زیر رو وارد میکنیم:
[hh]:mm
در واقع فرمت زمان هر چی که بود با ثانیه/بدون ثانیه یا مثلا AM/PM …. در هر صورت ساعت یا همون h رو داخل براکت [ ] قرار میدیم.
شکل ۲- محاسبات زمانی
حالا فرض کنید هر ساعت اضافه کاری معادل ۱۰۰۰۰ ریال هست، برای اینکه ببینیم در نهایت مبلغ ناشی از اضافه کاری چقدر خواهد بود، مجموع بدست آمده رو در مبلغ ضرب میکنیم.
همونطور که در شکل ۳ مشاهده می کنید در مرحله اول، حاصل ضرب مجموع ساعات در ۱۰۰۰۰ به فرمت ساعت هست، در حالیکه ما مبلغ را به فرمت عدد نیاز داریم. پس فرمت آن را به Number تغییر میدهیم. اما می بینیم که پس از تغییر فرمت به Number، میزان حاصلضرب درست نیست. علت همون مسئله ای سهت که در بالا به آن اشاره شد. اکسل مجموع ساعت را به روز تبدیل کرده (یعنی ۶۱:۰۵ رو تقسیم بر 24ساعت کرده) و حاصل را در ۱۰۰۰۰ ضرب میکند. پس اگر بخواهیم خود ساعت در ۱۰۰۰۰ ضرب بشه، باید حاصل را در ۲۴ ضرب کنیم.
شکل ۳- محاسبات زمان و ساعت
حالا که جنس زمان و منطق محاسبه اکسل رو فهمیدیم، به راحتی میتونیم هر نوع محاسباتی (جمع ساعت در اکسل) رو روی زمان های مختلف انجام بدیم، جمع، تفریق، ضرب و ….
در ادامه چند تابع کاربردی در خصوص زمان معرفی میکنم که در محاسبات مفید خواهد بود:
| ورودی تابع | تابع | خروجی | شرح |
| 9:56:10 PM | HOUR(B5) | 21 | این تابع ساعت را فراخوانی میکنه |
| 9:56:10 PM | MINUTE(B6) | 56 | این تابع دقیقه را فراخوانی میکنه |
| 9:56:10 PM | SECOND(B7) | 10 | این تابع ثانیه را فراخوانی میکنه |
| ۲۱ ساعت | ۵۶ دقیقه | ۱۰ ثانیه | TIME(D8,C8,B8) | 9:56:10 PM | این تابع زمان حاصل از ساعت، دقیقه و ثانیه ورودی رو نمایش میده |
مثلا زمانی که میخوایم دو ساعت رو از هم کم کنیم، از یکی از حالت های نشان داده شده در شکل ۴ استفاده میکنیم:
شکل ۴- محاسبه اختلاف زمان
در این مطلب در مورد جمع یا کسر ساعت صحبت کردیم. موضوعات زیر رو هم بهتون پیشنهاد میکنم بخونید:
سلام وقت بخیر من میخوام یه عملیاتی انجام بدم که در اون زمان که مفدار وارد شد زمان یاهمون تاریخ اعلام کنه
یکی یکی نه همه با هم یه مدل تاریخ باشه
درود بر شما
متوجه نشدم!
منظورتون زمان ثبت داده هست؟
سلام خسته نباشید ما یه فایل گزارش کارکرد داریم چطوری میشه ساعت اضافه کاری مثلا ۱۱:۳۸ دقیقه رو مثلا از نیم طاعت به بالا رند کرد که نتیجه بشه ۱۲ ساعت اضافه کاری
درود بر شما
=CEILING(A1,30/60/24)
اون عدد تبدیل ۳۰ دقیقه به یک روزه….تابع ceiling هم به مضرب و به بالا گرد میکنه
سلام. وقت بخیر. من یه فایل ثبت ورود و خروج دارم که ساعت ورود و ساعت خروج رو وارد میکنی، خودش محاسبه میکنه چند ساعت حضور داشته. تا اینجاش مشکلی نیست؛ مشکل اینجاست که اگه روزی ورودی و خروجی ساعت نداشته باشیم (مثلآ روز تعطیل، یا روزی که کلآ نیامدیم)، مدت زمات حضور رو ۲۴:۰۰ ساعت محابسه میکنه که توی جمع کل، خیلی به مشکل میخورم. میشه راهنمایی کنید چه کنم که این مشکل حل بشه؟
درود بر شما
با if کنترل کنید هر جا روز تعطیل بود خالی یا صفر بذاره
سلام وقت بخیر
من نیاز دارم در اکسل ستونی داشته باشد که میزان ساعتی رو که فرد دچار بیخوابی هست رو بیارم و تاثیر اون میزان ساعت رو بر مسائل دیگه بسنجم، نوع اعداد مورد استفاده برای این ستون باید تایم باشه یا چیز دیگه؟ ممنون میشم پاسخ بدید
سلام
اگه Time باشه میتونید بر اساس مطالبی که در این مقاله گفته شده محاسبات زمان رو راحت انجام بدید.
درود بر شما
بستگی به اعداد و نحوه مقایسه و تحلیل شما داره
در هر صورت، تایم هم نوعی عدد و د رنهایت قابل محاسبه هست
سلام. خسته نباشید.
میخوام تو یه سلول اکسل زمان رو به ساعت داشته باشم. یعنی وقتی شروع میشه مجموع ساعتهایی که میگذره رو بهم نشون بده. مثلا اگه از الان فعال شد، فردا همین موقع زده باشه ۲۴. و همینطور ادامه پیدا کنه. راهنماییم میکنید؟
درود
today رو منهای تاریخ شروع مد نظر کنید
یا now() رو
هر کدوم که مایلید
جواب رو به ساعت یا روز یا هر چیز دیگه ای که میخواید نمایش بدید
باسلام و تشکر از مطلب خوبتون
اگر بخوام در اکسل کسر کار رو به صورت زمان منفی نشون بدم چکار باید کنم ؟ راهی وجود داره ؟ مثلا بگم منفی یک ساعت و بیست دقیقه .
درود
برای نمایش زمان و تاریخ منفی، از excel option/ advance تیک ۱۹۰۴ date system رو بزنید
با تشکر ار پاسختون . ولی باز هم توی سلولی که میخوام ساعت منفی بنویسیم اینو میذاره #VALUE!
اگر ساعت فرمت تایم داشته باشه هیچ مشکلی نیست
فرمت رو چک کنید و مفهوم ساعت رو دقت کنید
نه واقعا دقت کردم . همین پیغامو میده
میشه یه فایل برای ایمل کنید ؟ یا ایمل بدین من براتون فایلمو بفرستم ؟
درود
میتونید در گروه تلگرامی مطرح کنید (لینک در فوتر سایت هست)
دوستان اونجا پاسخگو هستن
با سلام و وقت بخیر
در خصوص فرمول ساعت در اکسل سوال داشتم خدمتتون….در فایل اکسل، دو ستون داریم که یکی ساعت ورود و دیگری ساعت خروج رو نشون میده …می خواهیم فرمولی بنویسم که ساعت خروج را از ورود کسر کنه به صورتی که مدت زمان کارکرد نشون داده بشه …اما با شرط اینکه اگر ساعت ورود کمتر از ۷:۳۰ بود همان ۷:۳۰ در نظر گرفته شود برای مثال اگر ساعت ورود ۷:۳۰ و ساعت خروج ۱۷:۰۰ بود ۱۷:۰۰ منهای ۷:۳۰ .. اما اگر ورود ساعت ۷:۱۵ و خروج ساعت ۱۷:۰۰ نیز مجدداً فرمول به صورت ۱۷:۰۰ منهای ۷:۳۰ محاسبه شود.
ممنون میشم در این خصوص راهنمایی بفرمایید.
با سپاس
درود
باید با if ترکیب کنید
منطق ساعت رو که درک کنید میتونید با if حالت های مختلف رو محاسبه کنید
با سلام
با If فرمول زیر رو امتحان کردم اما متاسفانه برای ساعات کمتر از ۷:۳۰ عمل نمی کنه
فرمول مورد استفاده به شرح زیر هست:
(“F23-IF(“E23″>=”7:30”,E23,”7:30=
درود
به نظر میرسه ” ” زیادی گذاشتید . فرمول درست دیده نمیشه
دقت کنید سلول نباید در “” قرار بگیره
ضمن اینکه مقدار ساعت رو وقتی در “” میذارید به متن تبدیل میشه.
مقدار معادل عددی ۷:۳۰ رو بذارید. یعنی ۷.۵/۲۴
با تشکر از راهنمایی های فوق العادتون
وقتی جمع ساعات اضافه کاری بیشتر از ۱۰۰ میشه، حاصل جمع رو به صورت عدد ۱۰۰ نشون میده
ممنون میشم راهنماییم کنید که چجوری مشکلم رو حل کنم
مثال:
هر روز ۴ ساعت اضافه کاری و جمعه ها ۱۲ ساعت اضافه کاری که جمعا میشه ۱۵۲ ساعت ولی اکسل برای من ۱۰۰:۰۰:۰۰ رو نشون میده
درود بر شما
احتمال میدم که بعضی سلول هاتون جنس متنی دارند و در محاسبات دخیل نیستند
در غیر اینصورت هیچ مشکلی وجود نداره و اصلا ۱۰۰ محدودیتی نیست برای شمارش زمان
سلام
یه سوال داشتم :
فرمت سلول رو روی time > 13:30:55 میزارم و بعد باهم جمعشون میکنم ، اما وختی چهار تا ساعت تکراری باشن ، اونارو جمع نمیکنه و > 00:00:00 نشون میده .
چجوری درستش کنم ؟ که چهار تا ساعت تکراری هم باهم جمع کنه ؟
درود بر شما
ربطی به ساعت تکراری نداره
من انجام دادم و مشکلی نبود
باید ببینید کجای کار اشتباه میشه
با سپاس
وقتی از فرمول زیر استفاده می کردم جواب نمی گرفتم و جواب ۱۰۰:۰۰:۰۰ رو توو سلول نشون می داد
=sum(E4:E33)
حالا بدون تغییر توو فرمت سلول هام اومدم از فرمول زیر استفاده کردم و جواب درست رو گرفتم
=(E4+E5+E6…………..+E32+E33)
برام خیلی جالبه که چرا توو حالت دسته جمعی E4:E33 جواب درست نمی داد ولی توو حالت جمع سلول به سلول درست شد
خیلی ممنون میشم این ابهام رو برای من برطرف کنید
پس مطمئن شدم مشکل فرمت هست
وقتی از + استفاده میکنید، خودش فرمت رو تبدیل میکنه
اما تابع نه
پس چند سلول فرمت متنی دارند
برای تبدیل متن به عدد میتونید مقاله زیر رو بخونید
https://excelpedia.net/text-to-number/
با سلام
لطفا فرمول جمع ستون مرخصی با فرمت روز ، ساعت ،دقیقه را بفرمایید لازم به ذکر است در بین اعداد مانده مرخصی منفی هم وجود دارد.مثال زیر
هر چقدر در اینترنت جستجو کردم فرمول آن را پیدا نکردم یا اگر هم بود جواب درست نمی داد لطفا راهنمایی بفرمایید.
باتشکر
جمع مانده مرخصی : از چپ به راست روز ، ساعت ، دقیقه می باشد لطفا بفرمایید جمع کل ستون چقدر می شود؟
۴:۰۵:۳۱
۶:۰۲:۴۰
-۱:۰۳:۰۹
۲:۰۲:۲۳
۱:۰۱:۱۹
-۲:۰۲:۴۳
۰:۰۴:۵۰
درود بر شما
این فرمت که استفاده شده ساعت در اکسل هست یعنی ساعت دقیقه و ثانیه که اگر با روش های معمولاستفاده کنید جواب نیاز شما رو نمیده
بهتره اول تفکیک کنید
(یا با فرمو لهای متنی یا ابزار flash fill یا text to column و بعد روز ها با هم و ساعت و دقیقه با هم جمع کنید و به هم تبدیل کنید)
سلام. سپاس ،عالی هستید،زنده باشید.
در یک ستون ساعت تماس مشتری که البته هرفایل مربوط به یک ماه است و در ستون بعدی نشون میده کدوم روز هفته است ،الان برای اینکه بخواهیم جدولی داشته باشیم که نشون بده مثلاً در شنبه های فلان ماه مابین ساعت هفت تا هشت چه تعداد تماس داشتیم چه باید کرد.ببخشید طولانی شد.
درود بر شما
از تابع Countifs استفاده کنید و دو بازه زمان و روز هفته رو قرار بدید
و منطق زمان رو در تعیین شرط ها دخالت بدید