نحوه محاسبه زمان در اکسل – اختلاف زمان، جمع/تفریق زمان

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

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

• نحوه‌ی محاسبه اختلاف زمان در اکسل (زمان سپری‌شده)
• نحوه‌ی محاسبه و نمایش زمان‌های منفی در اکسل
• افزودن و کم کردن ساعت، دقیقه و ثانیه به یک زمان
• نحوه‌ی جمع چند زمان در اکسل

نحوه‌ی محاسبه اختلاف زمان در اکسل (زمان سپری‌شده)

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

فرمول 1. یک زمان را از دیگری تفریق کنید

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

ساده‌ترین و واضح‌ترین فرمول اکسل برای محاسبه‌ی اختلاف زمان این است:

‎=زمان پایان – زمان شروع

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

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

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

برای اعمال قالب زمانی سفارشی، بر روی Ctrl + 1 کلیک کنید تا پنجره‌ی محاوره‌ای Format Cells باز شود، Custom را از لیست Category انتخاب کنید و کدهای زمان را در کادر Type تایپ کنید.

حال، بیایید ببینیم که فرمول اختلاف زمان و کدهای زمانی ما در ورک‌شیت‌های واقعی چگونه کار می‌کند. با زمان شروع ساکن در ستون A و زمان‌های پایان در ستون B، می‌توانید فرمول زیر را در ستون‎های C تا E کپی کنید:

=$B2-$A2

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

فرمول 2. محاسبه‌ی اختلاف زمان با تابع TEXT

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

• ساعت بین دو زمان را محاسبه کنید: =TEXT(B2-A2, “h”)
• ساعت‌ها و دقیقه‌های بین 2 زمان برگردانید: =TEXT(B2-A2, “h:mm”)
• ساعت‌ها، دقیقه‌ها و ثانیه‌های بین 2 زمان را برگردانید: =TEXT(B2-A2, “h:mm:ss”)

فرمول 3. محاسبه‌ی ساعت‌ها، دقیقه‌ها یا ثانیه‌های بین دو زمان

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

محاسبه‌ی ساعت بین دو زمان:

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

‎=(زمان پایان – زمان شروع) * 24

با فرض این‌که زمان شروع شما در A2 باشد و زمان پایان در B2 است، می‌توانید از یک معادله‌ی ساده B2-A2 استفاده کنید تا اختلاف بین دو زمان را محاسبه کرده و سپس آن را در 24 ضرب کنید که تعداد ساعات در یک روز است:

=(B2-A2) * 24

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

=INT((B2-A2) * 24)

کل دقایق بین دو زمان:

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

‎=(زمان پایان – زمان شروع) * 1440

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

=(B2-A2)*1440

کل ثانیه‌های بین دو زمان:

برای به دست آوردن کل ثانیه‌های بین دو زمان، اختلاف زمان را 86400 ضرب می‌کنید، یعنی تعداد ثانیه در یک روز (24 ساعت * 60 دقیقه * 60 ثانیه = 86400).

‎=(زمان پایان – زمان شروع) * 86400

در مثال ما فرمول به شرح زیر است:

=(B2-A2)* 86400

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

فرمول 4. محاسبه‌ی اختلاف در یک واحد زمانی و نادیده گرفتن بقیه

برای پیدا کردن تفاوت بین 2 زمان در واحد زمانی خاص و نادیده گرفتن بقیه، از یکی از توابع زیر استفاده کنید.

• تفاوت ساعت‌ها، نادیده گرفتن دقیقه‌ها و ثانیه‌ها: =HOUR(B2-A2)
• تفاوت دقیقه‌ها، نادیده گرفتن ساعت‌ها و ثانیه‌ها: =MINUTE(B2-A2)
• تفاوت ثانیه‌ها، نادیده گرفتن ساعت و دقیقه: =SECOND(B2-A2)

هنگام استفاده از توابع HOUR، MINUTE و SECOND، لطفاً به خاطر داشته باشید که نتیجه نمی‌تواند بیش از 24 ساعت و 60 دقیقه برای دقیقه و ثانیه باشد.

توجه: اگر زمان پایان کمتر از زمان شروع باشد (یعنی نتیجه‌ی فرمول یک عدد منفی باشد)، خطای #NUM! برگردانده می‌شود.

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

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

با فرض این‌که تاریخ و زمان شروع در سلول A2 است، فرمول =NOW()-A2 نتایج زیر را برمی‌گرداند، مشروط بر این‌که در این مثال فرمت زمانی مناسب را در ستون B، یعنی h:mm اعمال کرده باشید:

در صورتی که زمان سپری‌شده بیش از 24 ساعت باشد، از یکی از این فرمت‌های زمانی استفاده کنید، به عنوان مثال d “روز” h:mm:ss مانند تصویر زیر:

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

=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2

فرمول 5. نمایش اختلاف زمان به صورت “XX روز، XX ساعت، XX دقیقه و XX ثانیه”

این احتمالاً کاربرپسندترین فرمول محاسبه‌ی اختلاف زمان در اکسل است. برای محاسبه‌ی اختلاف به روز از تابع INT و برای برگرداندن ساعت، دقیقه و ثانیه از توابع HOUR، MINUTE و SECOND استفاده می‌کنید؛ و سپس، تمام این توابع را در یک فرمول واحد به همراه برچسب‌های متن به هم می‌چسبانید:

=INT(B2-A2) & ” روز، ” & HOUR(B2-A2) & ” ساعت، ” & MINUTE(B2-A2) & ” دقیقه و ” & SECOND(B2-A2) & ” ثانیه”

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

=IF(INT(B2-A2)>0, INT(B2-A2) & ” روز, “،””) & IF(HOUR(B2-A2)>0, HOUR(B2-A2) & ” ساعت, “،””) & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) & ” دقیقه و “،””) & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” ثانیه”,””)

سینتکس آن ممکن است بیش از حد پیچیده به نظر برسد، اما کار می‌کند 🙂

از طرف دیگر، می‌توانید اختلاف زمان را با کم کردن زمان شروع از زمان پایان (به عنوان مثال =B2-A2) محاسبه کرده و سپس فرمت زمان زیر را در سلول اعمال کنید:

d “روز،” h “ساعت،” m “دقیقه و” s “ثانیه”

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

نحوه‌ی محاسبه و نمایش زمان‌های منفی در اکسل

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

روش 1. سیستم تاریخ اکسل را به سیستم تاریخ 1904 تغییر دهید

سریع‌ترین و آسان‌ترین راه نمایش زمان منفی به طور عادی (با علامت منفی) تغییر به سیستم تاریخ 1904 است. برای انجام این کار، روی File> Options> Advanced کلیک کنید، به بخش When calculating this workbook و تیک جعبه‌ی Use 1904 date system را بزنید.

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

روش 2. محاسبه‌ی زمان منفی در اکسل با فرمول

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

=IF(A2-B2>0, A2-B2, “-” & TEXT(ABS(A2-B2),”h:mm”))

=IF(A2-B2>0, A2-B2, TEXT(ABS(A2-B2),”-h:mm”))

هر دو فرمول بررسی می‌کنند که آیا اختلاف زمان (A2-B2) از 0 بیشتر است یا خیر، و اگر باشد این تفاوت را برمی‌گرداند. اگر اختلاف زمان کمتر از صفر باشد، فرمول اول قدر مطلق اختلاف را محاسبه می‌کند و علامت منها را به آن می‌چسباند. فرمول دوم با استفاده از فرمت منفی “-h:mm” دقیقاً نتیجه‌ی مشابه را به دست می‌آورد.

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

افزودن و کم کردن زمان در اکسل

اصولاً 2 روش برای اضافه کردن و کم کردن زمان در اکسل وجود دارد:

• با استفاده از تابع TIME
• استفاده از محاسبات حسابی بر اساس اعداد ساعت (24)، دقیقه (1440) و ثانیه (86400) در یک روز

تابع TIME(hour, minute, second) محاسبات زمان اکسل را بسیار آسان می‌کند، اما اجازه نمی‌دهد بیش از 23 ساعت یا 59 دقیقه یا 59 ثانیه اضافه یا کم شود. اگر با فواصل زمانی بزرگتر کار می‌کنید، از یکی از محاسبات حسابی در زیر استفاده کنید.

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

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

تابع TIME برای اضافه کردن کمتر از 24 ساعت

‎=زمان شروع + TIME(N ساعت, 0, 0)

به عنوان مثال، اگر زمان شروع کار شما در سلول A2 است و می‌خواهید 2 ساعت به آن اضافه کنید، فرمول به شرح زیر است:

=A2 + TIME(2, 0, 0)

توجه: اگر سعی کنید بیش از 23 ساعت با تابع TIME اضافه کنید، ساعت‌های مشخص‌شده بر 24 تقسیم می‌شود و باقی‌مانده به مقدار زمان شروع اضافه می‌شود. به عنوان مثال، اگر سعی کنید با استفاده از فرمول =A4 + TIME(25, 0, 0) مقدار 25 ساعت به “6/2/2015 10:00 AM” (سلول A4) اضافه کنید، نتیجه می‌شود “06/02/2015 11:00” یعنی A4 + 1 ساعت.
فرمول برای اضافه کردن هر عدد ساعت (کمتر یا بیشتر از 24 ساعت)

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

‎= زمان شروع + (N ساعت / 24)

به عنوان مثال، برای افزودن 28 ساعت به زمان شروع در سلول A2، فرمول زیر را وارد کنید:

=A2 + (28/24)

برای کم کردن ساعت (تفریق) از زمانی معین، از فرمول‌های مشابه استفاده می‌کنید و فقط “+” را با علامت منها جایگزین می‌کنید:

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

=A2-(3/24)

=A2-TIME(3,0,0)

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

نحوه‌ی اضافه / کم کردن دقیقه به زمان در اکسل

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

برای اضافه یا تفریق کردن کمتر از 60 دقیقه

از تابع TIME استفاده کنید و دقایقی را که می‌خواهید اضافه یا تفریق کنید در آرگومان دوم بنویسید:

‎‎=زمان شروع + TIME(0,  N دقیقە, 0)

و در این‌جا چند فرمول واقعی برای محاسبه‌ی دقایق در اکسل وجود دارد:

برای اضافه کردن 20 دقیقه به زمان در A2 فرمول: =A2 + TIME(0,20,0)

برای کم کردن 30 دقیقه از زمان در A2 فرمول: =A2 – TIME(0,30,0)

برای اضافه یا تفریق کردن بیش از 60 دقیقه

در محاسبه‌ی خود، تعداد دقایق را بر 1440 که تعداد دقایق در روز است، تقسیم کنید و مقدار شروع را به زمان شروع اضافه کنید:

‎‎=زمان شروع + (N دقیقە / 1440)

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

برای اضافه کردن 200 دقیقه: =A2 + (200/1440)

برای کم کردن 300 دقیقه: =A2 -(300/1440)

نحوه‌ی اضافه / تفریق کردن ثانیه به زمانی معین

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

برای اضافه کردن کمتر از 60 ثانیه به زمانی معین، می‌توانید از تابع TIME استفاده کنید:

‎=زمان شروع + TIME(0, 0, N ثانیه)

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

‎=زمان شروع + (N ثانیه / 86400)

برای کم کردن ثانیه، از فرمول‌های مشابه با علامت منها (-) به جای به‌علاوه (+) استفاده کنید.

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

برای اضافه کردن 30 ثانیه به A2: =A2 + TIME(0,0,31)

برای اضافه کردن 1200 ثانیه به: =A2 + (1200/86400)

برای کم کردن 40 ثانیه از: =A2 – TIME(0,0,40)

برای کم کردن 900 ثانیه از: =A2 – (900/86400)

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

فرمول جمع زمان اکسل یک تابع معمول SUM است و استفاده از فرمت زمان مناسب برای نتیجه، همان چیزی است که باعث می‌شود نتیجه به درستی نمایش داده شود.
به فرض شما چندین زمان پروژه در ستون B دارید و می‌خواهید آنها را جمع کنید. شما یک فرمول SUM ساده مانند =SUM(B2:B4) می‌نویسید و نتیجه را در فرمت پیش فرض دریافت می‌کنید، مانند hh:mm:ss.

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

روی سلول کلیک راست کرده و Format Cells را در منوی محتوا انتخاب کنید، یا Ctrl + 1 را فشار دهید تا کادر محاوره‌ای Format Cells باز شود. از لیست Category گزینه‌ی Custom را انتخاب کنید و یکی از فرمت‌های زمانی زیر را در کادر Type تایپ کنید:

• برای نمایش زمان کل به صورت دقیقه و ثانیه: [m]:ss
• برای نمایش زمان کل به عنوان ثانیه: [ss]

نتیجه به شرح زیر خواهد بود:

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

به منظور جمع کردن بیش از 24 ساعت، از همان فرمول SUM همان‌طور که در بالا بحث شد استفاده کنید، اما یکی از قالب‌های زمانی زیر را در سلول اعمال کنید:

برای دانستن این‌که چگونه این قالب‌های زمانی سفارشی در ورک‌شیت اکسل به نظر برسید، لطفاً به تصویر زیر نگاه کنید، جایی که یک فرمول SUM در سلول‌های A9 تا A13 وارد شده‌است:

=SUM($B$2:$B$4)