هر آنچه در کار با ساعت در اکسل باید بدانید
محاسبات مربوط به زمان (جمع ساعت در اکسل)
تاریخ و زمان در اکسل یکی از مباحثی است که خیلی مورد سوال قرار میگیره به عنوان مثال جمع ساعت در اکسل . مثلا یک برنامه برای ثبت ساعت ورود و خروج افراد تنظیم کردیم و میخواهیم از طریق اون محاسبات مربوط به حقوق، اضافه کاری، کسری کار و ….. محاسبه کنیم. پس باید بدونیم زمان در اکسل چه مفهومی داره و محاسباتش به چه صورته.
در این آموزش هر نکته ای راجع به زمان وجود داره رو با هم مرور خواهیم کرد.
مطابق شکل ۱ در دو سلول، زمان یکسان رو وارد کنید. فرمت یک سلول رو به 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 | این تابع زمان حاصل از ساعت، دقیقه و ثانیه ورودی رو نمایش میده |
مثلا زمانی که میخوایم دو ساعت رو از هم کم کنیم، از یکی از حالت های نشان داده شده در شکل ۴ استفاده میکنیم:
شکل ۴- محاسبه اختلاف زمان
در این مطلب در مورد جمع یا کسر ساعت صحبت کردیم. موضوعات زیر رو هم بهتون پیشنهاد میکنم بخونید:
با سلام من با نوشتن تابع IF برای سلول هایی که همه با فرمت [hh]:mm:nn مشکل دارم. همچین فرمولی بهش دادم :
=IF(D2>10,D2*10000,D2*20000)
اما درست حساب نمیکنه. مشکل از کجاست؟ سلول d2 هم درسته و فرمتش همون [hh]:mm:nn هستش.
خروجی هم بهم میده منتهی یا کل سلول هارو ضربدر ۱۰۰۰۰ میکنه یا ضربدر ۲۰۰۰۰ ، در واقع علامت کوچکتر بزرگتر ، همه ی اعداد رو فقط علامت کوچکترش رو میخونه.
سلام
فرمت سلول ها در انجام محاسبات تأثیری نداره و مهم ماهیت سلول هست. از نظر اکسل واحد زمان، روز هست حالا با هر فرمت که نمایش داده بشه فرقی نمیکنه. در شرطتون خواستید که مقدار سلول D2 از ۱۰ بیشتر باشه (که احتمالا منظورتون ۱۰ ساعت هست) اما از نظر اکسل این ۱۰ یعنی ۱۰ روز.
حالا وقتی شما تو سلول D2 مقدار ۱۰:۰۰ قرار میدید یعنی ۱۰/۲۴ روز که قطعا از عدد ۱۰ کوچکتر هست.
با این توضیحات مجددا فایلتون رو بررسی کنید.
سلام
من می خوام ساعت ۲ بامداد رو منهای ۳ ساعت کنم که جواب میشه ۲۳ ولی با خطا رو برو میشم. از چه فرمولی استفاده کنم که راحت تر و کم حجم تر باشه و جواب ۲۳:۰۰ بیاد ؟
۲:۰۰
-۳:۰۰
جواب : #### ! ولی باید ۲۳:۰۰ بیاد
درود..من بخوام ساعات ماموریتم جمع بخوره به چه صورت باید عمل کنم یعنی مثلا من ساعت ۸ صبح ۹۶/۱۲/۲۶ رفتم ماموریت تا ۱۸ عصر ۹۶/۱۲/۲۸ .. با چه فرمولی باید ساعت رو با در نظر گرفتن تاریخ جمع زد؟
سلام
کافیه تاریخ و ساعت برگشت رو منهای تاریخ و ساعت شروع مأموریت بکنید.
بزرگوار با چه فرمول و روشی.. به صورت تئوریک که بنده هم میدونم اما برای پیاده سازی در اکسل به چه صورت باید عمل کرد؟
واثعیت اینه که روش همینه واقعا. یعنی کافیه از هم کم کنید.
اما در خصوص میلادی اینطوری. برای شمسی باید ی سری کارها رو انجام بدید که در لینک قبل ارسال کردم.
اما بصورت کلی، بیاید روز اول و اخر رو کاری نداشته باشید. یعنی فقط ۹۶/۱۲/۲۷ رو در ۲۴ ضرب کنید. (اگر اد اینز نصب کنید و یا هر روش دیگه، اختلاف دو تارخی رو براحتی میتونید بدست بیارید).
حالا میمونه اول و آخر باشه، که روز اول رو باید ساعت ۱۲ شب رو از ساعت خروج از مبدا کم کنید(یعنی ۹۶/۱۲/۲۶ ساعت ۸. میشه ۱۶ ساعت ). ساعت روز آخرهم فقط کافیه خودش حساب بشه. یعنی همون ۱۸.
حالا کافیه این ۳ تا رو با هم جمع کنید.
باز هم میگم نکته اصلی در تاریخ شمسی بودن هست که حتما اون پست رو باید بخونید ….
موفق باشید
آخه چه جوری یک ساعت توی یک تاریخ رو از یک ساعت دیگه تو تاریخ متفاوت کم کنم؟؟
منظورتون از اداینز چیه؟ اگر ممکن برای این مباحث آموزش تصویری قرار بدید.
چند بار لینک گذاشتم(تاریخ شمسی)! ولی نمیخونید
آموزش تصویری هست
سوالتون و توی گروه مطرح کنید که بتونید فایل نمونه بذارید از داده هاتون….
هر داده ای شرایطی داره
من با برداشتی که داشتتم راه ارائه دادم.
حالا مممکنه این وسط داده هاتون الگوی خاصی داشته باشه.شاید نیاز باشه با توابع متنی تفکیک بشه و ….
موفق باشید
درود بر شما
فرمول ثابت و مشخصی نداره
با استفاده از توضیحات همین پست، فاصله تاریخی رو به روز حساب کنید و به ساعت تبدیل کنید
بعد با ساعت هر روز جمع کنید
باید با if فرمول نویس یکنید
ضمن اینکه تاریخ هاتون چون شمسیه، لینک تاریخ شمسی رو هم مطالعه کنید.
https://excelpedia.net/excel-jalali-date/
من مشکل اولیم حل شد.الان فقط اکسلم فاصله بین تاریخ های میلادی رو در میاره.یعنی تاریخ شمسی میدم ارور میده.اکسلمم ۲۰۱۶هست و اینکه تو اینترنت ی کد هم پیدا کردم اضافه کردم فانکشن شمسیو میاره ولی کار نمیکنه
خدا رو شکر.
اگر اون پست تاریخ شمسی رو مطالعه کردید و مشکلتون حل نشد، سوال رو در گروهتلگرام یمطرح کنیدتا بررسی بشه.
چون قاعدتا با اون لینک باید جواب بگیرید
موفق باشید
من با اون لینک نتونستم واقعا فاصله روزهای دو تاریخ رو به دست بیارم.میشه بیشتر راهنمایی کنید؟
در مورد تاریخ شمسی کاملا توضیح داده شده و فایل نمون ههم گذاشت هشده. دیگه .واقعا توضیح بیشتری وجود نداره. لازمه که با دقت خونده بشه.
اما در مورد مفهوم تاریخ، این پست رو هم بخونید کمک میکنه به درک مفهوم تاریخ.
https://excelpedia.net/excel-date-function/
با دقت بخونید حتما
اگر هم حل نشد، داخل گروه تلگرامی مطرح بفرمایید تا مشخص بشه مشکل کجاست. لینک گروه در فوتر صفحه اصلی سایت موجود هست.
وقتی به فرمت دو عدد وارد میکنیم ک از هم کم کنه اصلا جواب درست در نمیاد.بهتره خودتون امتحان کنید.
منظورتون چه فرمتی هست؟؟؟
فرمولی که ارائه شده برای ساعت هست نه تاریخ. (با توجه به کامنته ای قبلیتون، روی تاریخ دارید کار میکنید)
در مورد ساعت هم اون شکل ۴ که میبینید، خروجی اکسل هست. پس امتحان شده و جواب درست میده.
اگر دقیقا مطابق آموزش عمل کنید مشکلی وجود نخواهد داشت.
واضح بفرمایید چه عددی با چه شرایطی رو وارد میکنید که جواب درست نمیگیرید تا بررسی بشه.
موفق باشید
سلام مجدد.یعنی برای هرسلول باید کلیک راست کنم و اون فرمت رو وارد کنم؟تا تاریخم رو به صورت تاریخ نشون بده نه عدد؟
درود
مورد اول: شما فرمودید میخواید از هم کم کنید. قاعدتا وقتی کم میکنید نتیجه باید عدد باشه نه تاریخ. پس فرمت باید روی number باشه تا به عدد ببینید (تاریخ میلادی)
مورد دوم: برای انجام تنظیمات فرمت، نیازی نیست برای هر سلول این کار و بکنید. کافیه یکی رو انجام بدید و روی سلول های دیگه با format painter انتقال بدید
من میخوام توی اکسل دو تاریخ وارد کنم ماه و روز و سال مثلا بعد خودش روزهای بین این دو تاریخ رو محاسبه کنه و نشون بده.با در نظر گرفتن اینکه ماه ۳۰روزست ۲۹روزه یا ۳۱روزه.خیلی ممنون میشم کمک کنید
درود
اگر تاریخ میلادی هست
فقط کافیه از هم کم کنید. خ.دش درست محاسبه میکنه
اگر تاریخ شمسی هست
از لینک زیر استاده کنید:
https://excelpedia.net/excel-jalali-date/
از اینکه برای جواب دادن به سؤالم وقت گذاشتید خیلی سپاسگذارم.
یه چند روزی بود با این سؤال روبرو شده بودم ولی از هرکسی میپرسیدم چون مورد کاربردی نبود نمیتونست جواب بده
سلام. ببخشید چطور میتونم یه ساعت بزرگتر را از کوچکتر کم کنم و جواب را به صورت منفی دریافت کنم.
مثلاً:
۰۱:۰۰=۰۲:۰۰ + ۰۱:۰۰ – = ۰۵:۰۰ – ۰۴:۰۰
سلام
شما بعد از کم کردن ساعت بزرگتر، نتیجه را با ساعت دیگر جمع کردید که نتیجه یک ساعت مثبت میشه. اگر همه این فرمول رو در یک سلول بنویسید نهایتا خروجی درست رو میبینید (۰۱:۰۰) و نیاز به انجام کار خاصی نیست.
اما اگر این عملیات را مرحله به مرحله انجام بدید در قسمتی که ساعت منفی ایجاد میشه، نتیجه در سلول به صورت چند # به هم چسبیده دیده میشه. که در واقع چون خروجی غیر منطقی هست این کارکترها نمایش داده میشن.
برای اینکه به جای # خود ساعت منفی دیده بشه باید از مسیر زیر تیک گزینه Use 1904 date system رو بزنید:
FILE–> Options–> Advanced–> Editing Options