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

فرمت صحیح ساعت در اکسل چیست؟

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

برای تنظیم فرمت ساعت:

• سلول‌ها را انتخاب کنید.
• روی Format Cells کلیک کنید.
• از قسمت Time یا Custom، فرمت موردنظر را انتخاب کنید.

اگر فرمت سلول نادرست باشد، حتی درست‌ترین فرمول هم نتیجه درستی نمی‌دهد.

جمع ساعت در اکسل با فرمول ساده

در ساده‌ترین حالت، اگر چند ساعت کمتر از ۲۴ ساعت دارید، می‌توانید از تابع SUM منفعت گیری کنید. کافی است ساعت‌ها در چند سلول داخل شده باشند.

برای مثالً اگر ساعت‌ها در سلول‌های A1 تا A5 قرار دارند، فرمول به شکل زیر است:

در این حالت، اکسل مجموع ساعت‌ها را محاسبه می‌کند. اما اگر حاصل جمع از ۲۴ ساعت زیاد تر بشود، نتیجه به‌درستی نمایش داده نمی‌بشود. برای این حالت باید سراغ تنظیمات گسترش یافتهتر برویم.

جمع ساعت در اکسل زیاد تر از ۲۴ ساعت

یکی از مشکلات رایج، جمع ساعت‌هایی است که مجموع آن‌ها زیاد تر از ۲۴ ساعت می‌بشود. به‌طور پیش‌فکر، اکسل سپس از ۲۴ ساعت مجدد از صفر اغاز می‌کند.

برای حل این مشکل:

• فرمت سلول نتیجه را روی Custom قرار دهید.
• فرمت [h]:mm یا [h]:mm:ss را انتخاب کنید.

با این کار، اکسل تعداد کل ساعت‌ها را بدون ریست شدن نمایش می‌دهد. این روش برای محاسبه مجموع زمان پروژه‌ها یا شیفت‌های کاری زیاد کاربردی است.

جمع ساعت کاری در اکسل

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

بعد از محاسبه ساعت کاری هر روز، می‌توانید همه نتایج را با تابع SUM جمع بزنید. حتماً فرمت نهایی را روی [h]:mm تنظیم کنید تا مجموع دقیق نمایش داده بشود.

جمع ساعت و دقیقه در اکسل

اکسل ساعت و دقیقه را هم‌زمان حمایتمی‌کند. کافی است زمان‌ها به‌صورت صحیح داخل شوند. برای مثال ۰۲:۴۵ یا ۰۱:۳۰. در این حالت هم فرمول SUM بدون مشکل کار می‌کند. اگر مجموع دقیقه‌ها از ۶۰ عبور کند، اکسل به‌صورت خودکار آن را به ساعت تبدیل می‌کند. فقط فراموش نکنید که فرمت سلول نتیجه مناسب باشد.

جمع و تفریق ساعت در اکسل

برای جمع و تفریق ساعت، نیازی به تابع خاصی ندارید. اکسل با عملگرهای ریاضی ساده هم این کار را انجام می‌دهد.

برای مثال:

• جمع ساعت: =A1+B1
• تفریق ساعت: =B1-A1

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

محاسبه جمع ساعات بین رکوردهای وقتی با منفعت گیری از تابع IF

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

فکر کنید:

• ساعت اغاز در A2
• ساعت آخر در B2

فرمول محاسبه به این شکل است:

=IF(AND(A2<>"",B2<>""),B2-A2,"")

=IF(AND(A2<>"",B2<>""),B2-A2,"")

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

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

منفعت گیری از تابع MOD اکسل برای جمع ساعت‌ها

بعضی اوقات زمان جمع و تفریق ساعت در اکسل با مشکل نمایش زمان منفی یا عبور از نیمه شب روبه رو می‌شویم. در این شرایط، تابع MOD راه‌حل مناسبی است. اگر ساعت اغاز ۲۲:۰۰ و ساعت آخر ۰۶:۰۰ باشد، فرمول ساده B2-A2 نتیجه منفی می‌دهد. برای اصلاح آن از فرمول زیر منفعت گیری کنید:

=MOD(B2-A2,1)

=MOD(B2-A2,۱)

عدد ۱ نشان‌دهنده یک شبانه‌روز کامل است. تابع MOD علتمی‌بشود نتیجه همیشه در بازه ۰ تا ۲۴ ساعت باقی بماند. این روش برای شیفت‌های شبانه زیاد سودمند است.

محاسبه جمع ساعت در اکسل با تابع TEXT

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

=TEXT(C10,"[h]:mm")

=TEXT(C10,"[h]:mm")

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

تابع HOUR اکسل برای محاسبه جمع ساعات

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

اگر مقدار A1 برابر ۰۲:۴۵ باشد، نتیجه عدد ۲ خواهد می بود. اگر بخواهید فقط مجموع ساعت‌ها را بدون دقیقه محاسبه کنید، می‌توانید ابتدا زمان‌ها را جمع بزنید و سپس روی نتیجه از تابع HOUR منفعت گیری کنید. یقیناً دقت داشته باشید که HOUR فقط عدد بین ۰ تا ۲۳ را برمی‌گرداند. به این علت برای مجموع زیاد تر از ۲۴ ساعت مناسب نیست، مگر این که ابتدا فرمت سلول را به شکل صحیح تنظیم کرده باشید.

تابع NOW برای محاسبه مجموع ساعات سپری شده

تابع NOW زمان و تاریخ جاری سیستم را نمایش می‌دهد. این تابع برای محاسبه زمان زمان سپری شده از یک تاریخ یا ساعت اشکار کاربرد دارد. فکر کنید زمان اغاز در A1 به ثبت رسیده است. برای محاسبه زمان زمان سپری شده تا این لحظه از فرمول زیر منفعت گیری کنید:

بعد از داخل کردن فرمول، باید فرمت سلول را روی [h]:mm تنظیم کنید تا مجموع ساعت‌های سپری شده به‌درستی نمایش داده بشود. تابع NOW به‌صورت خودکار به‌روزرسانی می‌بشود. به این علت هر بار فایل را باز کنید، مقدار تازه محاسبه خواهد شد.

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

در کار با ساعت‌ها چند مسئله مهم وجود دارد. اول این که همیشه فرمت سلول‌ها را بازدید کنید. دوم این که برای زمان‌های زیاد تر از ۲۴ ساعت، حتماً از فرمت [h] منفعت گیری کنید. این چنین از داخل کردن ساعت به‌صورت متن خودداری کنید، چون اکسل آن را محاسبه نمی‌کند.

جمع‌بندی

جمع ساعت در اکسل، اگر با شناخت درست از ساختار زمان انجام بشود، کار پیچیده‌ای نیست. با اهمیت ترین مسئله، تنظیم صحیح فرمت سلول‌ها است. برای زمان‌های زیاد تر از ۲۴ ساعت باید از فرمت [h]:mm منفعت گیری کنید. در شرایط گسترش یافتهتر می‌توانید از توابعی همانند IF، MOD ،TEXT ،HOUR و NOW پشتیبانی بگیرید. با یادگیری این تکنیک‌ها، محاسبه ساعت کاری، شیفت‌های شبانه و مجموع زمان پروژه‌ها زیاد دقیق و ساده انجام می‌بشود.

سؤالات متداول درمورد جمع ساعت در اکسل