ساعت و زمان در اکسل — راهنمای کاربردی (+ دانلود فیلم آموزش رایگان)

۲۷۳۶۵ بازدید
آخرین به‌روزرسانی: ۰۸ اسفند ۱۴۰۲
زمان مطالعه: ۳۳ دقیقه
ساعت و زمان در اکسل — راهنمای کاربردی (+ دانلود فیلم آموزش رایگان)

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

997696

فیلم آموزشی ساعت و زمان در اکسل

دانلود ویدیو

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

ساعت و زمان در اکسل

محاسبات برمبنای اعدادی که برحسب ساعت هستند با اعداد معمولی متفاوت است. در شیوه نوشتن و انجام عملیات ریاضی، از سیستم عدد نویسی اعشاری (Decimal) یا ده-دهی استفاده می‌کنیم. ولی ساعت با توجه به تقسیم‌بندی روز به ۲۴ قسمت مساوی، مبنایی متفاوت در نمایش و محاسبات دارد. در حقیقت محاسبات ساعت باید بر اساس عدد نویسی برمبنای ۲۴ یا ۱۲ (نصف شبانه روز) صورت گیرد. همچنین دقیقه و ثانیه هم بر مبنای ۶۰ نوشته و محاسبه می‌شوند.

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

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

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

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

از قالب‌بندی سلول یا دستور Format Cell به منظور تغییر ظاهر مقادیر عددی استفاده می‌کنیم. به منظور دسترسی به پنجره Format Cell، پس از انتخاب ناحیه از سلول‌های کاربرگ که مقادیر عددی دارند. از برگه Home قسمت Number را انتخاب و از فهرست کشویی، نوع یا قالب نمایش اعداد را انتخاب کنید. گزینه‌های این لیست به مانند تصویر ۱ ظاهر خواهد شد. البته این کار را با کلیک راست و اجرای دستور Format Cells نیز می‌توانید انجام دهید.

formatcell time dialogbox in excel
تصویر ۱: پنجره تنطیمات قالب‌بندی ساعت و زمان در اکسل

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

جدول ۱: معرفی چند قالب برای نمایش تاریخ

ردیفقالب نمایشعملکردتوضیحات
۱*1:30:55 PMنمایش ساعت به شکل ۱۲ ساعتی با مشخص کردن صبح (AM) و بعد از ظهر (PM)جداکننده و نوع نمایش زمان و ساعت (ساعت، دقیقه، ثانیه) برحسب تنظیمات سیستم (Control Panel) مشخص می‌شود.
۲13:30نمایش فقط ساعت و دقیقه به شکل ۲۴ ساعتی
۳1:30 PMنمایش فقط ساعت و دقیقه به شکل 12 ساعتی
۴13:30:55نمایش ساعت، دقیقه و ثانیه به شکل ۲۴ ساعتی
۵1:30:55نمایش ساعت، دقیقه و ثانیه به شکل ۱۲ ساعتی
۶30:55.2فقط نمایش دقیقه و ثانیه به همراه دهم ثانیه
۷37:30:55نمایش ساعت، دقیقه و ثانیهمناسب برای نمایش ساعاتی که از ۲۴ بزرگتر هستند. مثلا نمایش حاصل جمع چندین ساعت

نکته: نمایش و ترتیب قرارگیری ساعت، دقیقه و ثانیه با توجه به موقعیت جغرافیایی تنظیم شده در مرکز کنترل (Control Panel) ممکن است تغییر کند. گزینه‌هایی که در لیست قالب‌بندی ساعت با علامت * مشخص شده‌اند، از تنظیمات سیستم عامل (Control Panel) تبعیت می‌کنند.

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

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

به این ترتیب واضح است که  عدد اعشاری ۰٫۵ باید بیانگر ساعت ۱۲ ظهر باشد. همچنین ساعت شش بعد از ظهر نیز به صورت یک عدد کسری به شکل ۰٫۷۵ در خواهد آمد. این تبدیل را به کمک قالب‌بندی سلول در اکسل از عدد به ساعت و برعکس می‌توان در تصویر ۲ و ۳ مشاهده کرد.

نکته: اگر ساعت بزرگتر از ۲۴ شود در حقیقت، عدد اعشاری دارای قسمت صحیح خواهد شد. در این صورت مقدار عدد صحیح بیانگر تاریخ و قسمت اعشاری هم ساعت را نشان می‌دهد. برای مثال عدد ۲۵٫۷۵ یک روز و ۰٫۷۵ روز را مشخص می‌کند؛ این یعنی که ساعت برابر با شش بعد از ظهر روز بعد است. ولی به کمک قالب‌بندی می‌توانید در صورت لزوم، نمایش ساعات بزرگتر از ۲۴ را هم اجرا کنید.

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

محاسبه روی مقادیر ساعت و زمان در اکسل درست به مانند محاسبات روی اعداد عادی است ولی با توجه به مبنای ۲۴ یا ۶۰ در دستگاه محاسبات زمان، نحوه نمایش نتایج متفاوت است. البته به این موضوع توجه داشته باشید که باید مقادیر ساعت و زمان را درون سلول‌های اکسل وارد کنید و به طور مستقل نمی‌توان از آن‌ها در نوشتن فرمول‌ها استفاده کرد.

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

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

D1:C1 B1 \large D1 : C1 -  B1

time sheet
تصویر ۲: جدول ساعات ورود و خروج به همراه محاسبه ساعات کاری

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

total time sheet
تصویر ۳: جدول کامل از ساعات کاری و مجموع ساعت کاری در پایان هفته

توجه داشته باشید که اگر قالب‌بندی سلول حاصل جمع را تغییر ندهید، مقدار ۲۲:۵۷ در آن ظاهر خواهد شد که برای کار ما مناسب نیست. در حقیقت این عدد اعشاری دارای بخشی نیز به عنوان عدد صحیح است که به عنوان روز و تاریخ در نظر گرفته شده ولی نمایش داده نمی‌شود. واضح است که مقدار صحیح ۴۶:۵۷ است که به معنی یک روز (۲۴ ساعت) و ۲۲ ساعت و ۵۷ دقیقه است.

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

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

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

E6:=D6245000 \large E6: = D6 * 24 * 5000

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

total time sheet payment
تصویر ۴: جدول محاسبات دستمزد و ساعات کاری در هفته

فقط به این موضوع نیز توجه داشته باشید که در سلول D8 ممکن است مقدار عددی توسط اکسل با قالب ساعت نمایش داده شود. برای مشاهده مقدار درست، قالب نمایش برای این سلول را به صورت General درآورید.

توابع مربوط به محاسبات ساعت و زمان در اکسل

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

جدول ۲: معرفی توابع مرتبط با محاسبات ساعت و تاریخ در اکسل به همراه کارکرد پارامترهای آن‌ها

ردیفنام تابععملکردپارامترهاتوضیحات
۱Hourاستخراج ساعت از یک مقدار زمانیSerial_numberپارامتر یک عدد اعشاری است.
۲Minuteاستخراج دقیقه از یک مقدار زمانیSerial_numberپارامتر یک عدد اعشاری است.
۳Nowثبت ساعت جاری سیستم در سلولبدون پارامترساعت و تاریخ جاری سیستم در سلول توسط این تابع ثبت می‌شود. مشابه تابع TODAY محسوب می‌شود.
۴Secondاستخراج ثانیه از یک مقدار زمانیSerial_numberپارامتر یک عدد اعشاری است.
۵Timeتبدیل ساعت به شکل یک عدد اعشاریhour, minute, secondعدد حاصل برای ساعات‌های بزرگتر از ۲۴ به شکل عدد صحیح و قسمت اعشاری نمایش داده می‌شود. پارامتر اول، ساعت، پارامتر دوم، دقیقه و پارامتر سوم هم ثانیه را مشخص می‌کند.
۶Timevalueتبدیل یک عدد اعشاری به ساعت، دقیقه و ثانیهtime_textعدد اعشاری به همراه قسمت صحیح، به صورت ساعت و تاریخ مشخص می‌شود.

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

مثال ۱

فرض کنید می‌خواهیم از اکسل به عنوان یک زمان‌سنج (StopWatch) استفاده کنیم. ساعت شروع را در خانه A2 وارد می‌کنیم. فرض کنید این ساعت برابر با ۸:۴۵ صبح است. حال در سلول A3 فرمولی به شکل زیر وارد می‌کنیم.

A3:=NOW()A2 \large A3: = NOW() - A2

به این ترتیب فاصله زمانی بین ۸:۴۵ دقیقه با زمان جاری سیستم محاسبه می‌شود. اگر می‌خواهید صدم ثانیه نیز مشاهده شود، قالب سلول A3 را به دلخواه و طبق جدول ۱ تغییر دهید. با فشردن هر بار کلید F9، محاسبات مربوط به سلول‌های کاربرگ، از نو انجام شده و زمان جاری سیستم از زمان شروع کسر می‌شود. در نتیجه فاصله زمان جاری از ساعت ۸:۴۵ دقیقه با زدن کلید F9 محاسبه و نمایش داده می‌شود.

stopwatch in excel
تصویر ۴: نمونه‌ای از زمان‌سنج در اکسل

مثال ۲

به منظور استخراج ساعت از یک عدد اعشاری (یا حتی زمان) از تابع Hour استفاده می‌کنیم. چنین کاری در تصویر ۵ مشاهده می‌شود. همانطور که می‌بینید سلول A2 که حاوی فرمول زیر است،  براساس ساعت بزرگتر از ۲۴، قالب‌بندی شده است.

show clock
تصویر ۵: نمایش ساعت برای یک عدد صحیح به همراه اعشار با قالب بزرگتر از ۲۴

خلاصه و جمع‌بندی

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

بر اساس رای ۲۹ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
مجله فرادرس
۱۹ دیدگاه برای «ساعت و زمان در اکسل — راهنمای کاربردی (+ دانلود فیلم آموزش رایگان)»

سلام
اگه بخوایم تاریخی که تو سلول وارد کردیم وقتی به تاریخ جاری سیستم رسید رنگ سلول عوض بشه راه حلی داره؟

سلام یک سول؟ اگر بخواهیم یک ساعت خاص در بازه زمانی جستجو کنیم مثلا 22 تا 6 صبح و این ساعات به عنوان شبکاری در نظر بگیریم چطور در محاسابات ساعات مختلف جستجو کنیم

خیلی ممنونم چون برام هم جالب بود و هم مفید
پاینده باشید.

با سلام کلیپ ارائه شده بسیار بسیار عالی بود
دوستم یه جا گیر افتاده بود سر دقیقه که چگونه باید تو فرمول اکسل پیداه کنه به من گفت منم بلد نبودم با کمک شما تونستم راه حل رو متوجه بشم بازم تشکر

درود! اصلا نمیفهمم که برای محاسبه دستمزد، چرا جمع ساعات کاری رو در 24 ضرب کردید؟؟!! مگه نباید تقسیم بر 24 کرد؟

با سلام؛

در بخشی از متن به این مورد اشاره شده است:

«ساعت به صورت یک عدد اعشاری بوده و کسری یا بخشی از روز را نشان می‌دهد. پس برای آن که ساعت را به عدد تبدیل کنیم باید آن را در ۲۴ ضرب کنیم تا به یک عدد صحیح تبدیل شده، سپس در محاسبات با اعداد دیگر به کار رود.»

با تشکر از همراهی شما با مجله فرادرس

سلام
یه مشکل دارم..
کسانی که شب کار هستن مثلاً ساعت ۱۷ ورود و ساعت ۳ بامداد خروج ..
واسه محاسبه ساعت کارکرد باید چه فرمولی داد که #### نیاد

سلام . وقتتون به خیر ؛ می خواستم ساعت و تاریخ رو اتوماتیک ثبت کنه و از تابع now استفاده کردم .اما لازم دارم مثلا هر 30 ثانیه تایم رو اصلاح کنه ..
ممنون می شم راهنماییم کنید.

سلام . وقتتون به خیر ؛ می خواستم ساعت و تاریخ رو اتوماتیک ثبت کنه و از تابع now استفاده کردم .اما لازم دارم مثلا هر 30 ثانیه تایم رو اصلاح کنه .
ممنون می شم راهنماییم کنید.

سلام
من سه تا ستون دارم با محتوای عددی مثلا ستون ساعت با عدد 47 و ستون دقیقه با عدد 34 و ثانیه با عدد 20 حالا میخوام تو ستون چهارم با این سه تا عدد یک فرمت زمانی داشته باشم که به صورت 47:34:20 نمایش بده و قابلیت جمع و تفریق داشته باشه ممنون میشم راهنماییم کنید.

عالی
ممنون

باسلام و وقت بخیر خیلی عالی بود

ی سوال دارم مثلا میشه تعداد اقامت روز کارمند رو هم محاسبه کرد؟؟ منظورم اینکه ی کارمند فلان تاریخ میاد و روزانه اکسل تعداد روزی ک اقامت در اداره زده رو محاسبه کنه؟؟

سلام
بسیار عالی و کاربردی

بسیار عالی و کاربدی

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

سلام خواننده گرامی،

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

پیروز و موفق باشید.

یک مسئله دارم. اذان مغرب روز پانزدهم ۲۰:۲۴ دقیقه هست اذان صبح روز شانزدهم ۴:۴۸ دقیقه هست. ازمون خواستن که فاصله زمانیشو بدست بیاریم و بر ۱۲ تقسیم کنیم و بگیم که پایان قسمت ششم یا همون ابتدای قسمت هفتم چه ساعتی میشه؟ چجوری باید محاسبه کنم؟

خوب بود ولی توضیح ساعت خروج کمتر از ورود رو نگفتی استاد

سلام و درود،

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

تندرست و پاینده باشید.

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *