هر آنچه در کار با ساعت در اکسل باید بدانید
محاسبات مربوط به زمان (جمع ساعت در اکسل)
تاریخ و زمان در اکسل یکی از مباحثی است که خیلی مورد سوال قرار میگیره به عنوان مثال جمع ساعت در اکسل . مثلا یک برنامه برای ثبت ساعت ورود و خروج افراد تنظیم کردیم و میخواهیم از طریق اون محاسبات مربوط به حقوق، اضافه کاری، کسری کار و ….. محاسبه کنیم. پس باید بدونیم زمان در اکسل چه مفهومی داره و محاسباتش به چه صورته.
در این آموزش هر نکته ای راجع به زمان وجود داره رو با هم مرور خواهیم کرد.
مطابق شکل ۱ در دو سلول، زمان یکسان رو وارد کنید. فرمت یک سلول رو به 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 | این تابع زمان حاصل از ساعت، دقیقه و ثانیه ورودی رو نمایش میده |
مثلا زمانی که میخوایم دو ساعت رو از هم کم کنیم، از یکی از حالت های نشان داده شده در شکل ۴ استفاده میکنیم:
شکل ۴- محاسبه اختلاف زمان
در این مطلب در مورد جمع یا کسر ساعت صحبت کردیم. موضوعات زیر رو هم بهتون پیشنهاد میکنم بخونید:
سلام خواهش میکنم اگه امکان داره فرمول ساعت ورود.ساعت خروج .جمع کارکرد . جمع کارکرد ضربدرمبلغ رابرای من ارسال کنید ممنون میشم تو فرمول ها متوجه نشدم کامل چه کار باید کرد با تشکر فراوان
اگر به خوبی مطالعه کنید، کامل توضیح داده شده
ولی به هر حال میتونید فایل رو درست کنید
اشکالات رو در گروه تلگرامی مطرح کنید
با سلام ضمن تشکر از مطالب مفید سایتتون ، سوال من اینه که آیا در اکسل راهی وجود داره که دقیقه سه رقمی ثبت کنیم و در یک سلول جمع این دقایق به ما داده بشه .
درود بر شما
خب میتونید مثل عدد ثبت کنید
عدد معمولی
اگر هم ظاهر رو خواستید تنظیم کنید، با فرمت سل میتونید اینکار و بکنید
سلام خواهش میکنم اگه امکان داره فرمول ساعت ورود.ساعت خروج .جمع کارکرد . جمع کارکرد ضربدرمبلغ رابرای من ارسال کنید ممنون nasrollahbaneshi@Gmail.com با تشکرفراوان
درود بر شما
همه اطلاعات مورد نیازتون در این آموزش کنجانده شده.
مقاله را کامل مطالعه کنید و مسئله مورد نظر خودتون رو حل کنید
خیلی متن عالی بود . کاملا مشکل من را حل کرد .
سلام. با تشکر از مطالب خوب، کامل و کاربردیتون. من توی فرمول نویسی به یک مشکل برخوردم اگه لطف کنید راهنماییم کنید ممنون میشم.
میخوام یک برنامه آنالیز قیمت بنویسم و سلول رو به صورت لیست درآوردم و میخوام با انتخاب هر آیتم از لیست قیمتشو جلوش نشون بده باید چیکار کنم؟
درود بر شما
از VLOOKUP استفاده کنید
https://excelpedia.net/vlookup-function/
سپاس
سلام
با استفاده از فرمول =D12-C12+(D12<=C12) زمان کاری را محاسبه میکنم
وقتی که هیچ داده ای به ورودی و خروجی یعنی سلول C و D نمی دهم ساعت را ۲۴:۰۰ محاسبه میکنه که می خواهم ۰۰:۰۰ باشه
با فرمول =IF(OR(C13-D13=M13؛D13-C13=M13)؛""؛(D13-C13+(D13<=C13))) کار نمی کنه یعنی "" نمی شود
لطفا راهنمایی بفرمایید.
سلام خسته نباشید
برای جدا کردن ساعت از دقیقه زمانی که ساعت اعلامی اضافه کار ماهیانه باشد و عدد ساعت از ۲۴ ساعت بیشتر باشه مثلا ۱۲۰ ساعت فرمول ساعت عمل نمی کنه باید از چه فرمولی استفاده کنم که بتونم ساعت و دقیقه رو در دو سل جداگانه داشته باشم ، چون میخوام مبلغ اضافه کار رو بدست بیارم
سلام، تشکر
برای بدست آوردن ساعت میتونید از فرمول زیر استفاده کنید:
برای بدست آوردن دقیقه هم از فرمول زیر استفاده کنید:
کلا نرم افزار اکسل تاریخ شمسی رو نمیخونه، شما باید یه فایل ماکرو درست کنین که این تاریخ ها رو شناسایی کنه خودش، بعدش تو فرمت سل میرین و در قسمت custum این فرمتو براش انتخاب میکنین ( ۰۰۰۰″/”۰۰″/”۰۰). در ضمن اون فایل ماکرو رو میتونین تو گوگل سرچ کنین.
درود بر شما
نسخه ۲۰۱۶ فرمت تاریخ شمسی رو پشتیبانی میکنه. اما توابع تاریخ رو خیر.
بله یک راهه همین استفاده از عدد و فرمت سل هست و یک راه دیگه اداینز که در این لینک توضیحات لازم ارائه شده.
https://excelpedia.net/?s=%D8%AA%D8%A7%D8%B1%DB%8C%D8%AE+%D8%B4%D9%85%D8%B3%DB%8C
با سلام و تشکر از زحمات شما اساتید گرانقدر
بنده سوالی داشتم می خواستم به طور مثال اگر دقیقه یک ساعتی از ۳۰ بیشتر باشد آن زمان را به بالا گرد کند مثلا ۱۲۰:۳۵ را ۱۲۱:۰۰ تحویل دهد.
در این خصوص فرمول(IF(MINUTE(A1)>=30;HOUR(A1)+1;HOUR(A1 = اما جواب در ست نمی دهد لطفا کمکم فرمایید.
دورد بر شما
این یکی از راه هاست.
جناب چراغی از راهنمایی بابت مشکل (#)در تفریق ساعت ممنونم