محاسبه اضافه کاری و ساعت تاخیر در اکسل+دانلود فایل
یکی از مهمترین و چالش برانگیزترین عملیات حسابداران در بحث محاسبه حقوق و دستمزد، مربوط به محاسبه ساعت کاری پرسنل از جمله: محاسبه ساعت اضافه کاری، ساعت کار عادی، ساعت تاخیر، جمعه کاری، حق ماموریت ،تعجیل و پس از آن محاسبه مبلغ اضافه کاری در اکسل می باشد.مهمترین بحثی که درمورد محاسبه ساعت باید بدان اشرافیت داشت منطق محاسبات ساعت در اکسل می باشد که قبلا در پستی جداگانه مفصل به این موضوع پرداخته شده است.
کاری که ما در این آموزش قرار است انجام دهیم، محاسبه ساعت کارکرد پرسنل، ساعت تاخیر، ساعت اضافه کاری و در نهایت محاسبه مبلغ اضافه کاری در اکسل طبق جدول زیر (شکل شماره ۱) می باشد.اما قبل از شروع آموزش لازم است با یکسری اصطلاحات و مبانی حقوق و دستمزد آشنا شویم.
اگه میخوای بصورت ویدئوی آموزشی نحوه فرمولنویسی محاسبه کارکرد پرسنل رو یاد بگیری حتما آموزش فرمولنویسی محاسبه ساعت کارکرد پرسنل رو دانلود کن.
اگه میخوای یک داشبورد بسیار حرفه ای برای ورود و خروج و کارکرد پرسنل طراحی کنی حتما دوره طراحی داشبورد حرفهای ورود و خروج پرسنل رو دانلود کن.
تعریف ساعت کار:
طبق ماده ۵۱ قانون کار، ساعت کار مدت زمانی است که کارگر نیرو یا وقت خود را به منظور انجام کار در اختیار کارفرما قرار می دهد. ساعات صرف صبحانه و نهار جزء ساعت کار به حساب نمیآید.
نحوه بدست آوردن ساعت کار موظفی:
ساعت کار موظفی روزانه طبق قانون کار بصورت هفتگی محاسبه می شود.در مشاغل عادی(بجز مشاغل سخت و زیان آور) ساعت کار باید ۴۴ ساعت در هفته به ازای ۶ روز باشد.یعنی اگر ما ۴۴ ساعت را در ۶ تقسیم کنیم مقدار …۷.۳۳۳۳۳۳۳ ساعت یا همان ۷ ساعت و ۲۰ دقیقه (۷:۲۰) بدست می آید که همان ساعت کاری روزانه می باشد.یعنی ساعت کار عادی هر شخص روزانه ۷ ساعت و ۲۰ دقیقه می باشد ونباید از ۸ ساعت در روز تجاوز کند.
محاسبه ساعت کار موظفی در اکسل:
همانطور که در شکل شماره ۱ مشاهده می کنید ما سلول F2 را به عنوان ساعت شروع کار(۸:۰۰) و سلول D2 را به عنوان ساعت پایان کار(۱۶:۰۰) درنظر گرفته ایم تا براحتی قابل تغییر باشد.حال می خواهیم در سلول B2 ساعت کار موظفی کارگر را در فرم فوق محاسبه کنیم.فرمول زیر را در سلول B2 بنویسید:
D2-F2=
محاسبه ساعت کارکرد عادی در اکسل:
ما براحتی با تفریق کردن ساعت خروج و ساعت ورود می توانیم میزان ساعت کارکرد روزانه را بدست آورد.اما اگر ما اینگونه محاسبه کنیم ساعت اضافه کاری و کارکرد عادی باهم قاطی می شوند و حتما باید اضافه کاری از ساعت کارکرد عادی جدا شود.ما با استفاده از تابع IF شرایطی را لحاظ می کنیم که این دو مورد باهم تداخل نداشته باشند.
پس برای محاسبه ساعت کارکرد عادی در سلول F5 فرمول زیر را نوشته و به سلول های پایینی درگ کنید. توجه داشته باشید که در فرمول زیر فرض شده است که ساعت ورود پرسنل حتما باید از ساعت ۸:۰۰ به بعد باشد و حضور زودتر پرسنل از موعد مقرر به هیچ عنوان اضافه کاری محاسبه نمی شود.
(IF((E5-D5)>$B$2,$D$2-D5,E5-D5=
نقصی که در فرمول فوق وجود دارد این است که کارکرد روزهای جمعه را نیز بعنوان کارکرد عادی درنظر می گیرد.درحالی که کل ساعات کاری در روز جمعه باید اضافه کاری تلقی شود.پس فرمول را مطابق شکل شماره ۳ اصلاح می کنیم:
تعریف اضافه کاری:
درصورتی که ساعات کاری کارگر خارج از ساعت کاری تعریف شده باشد، مازاد آن اضافه کاری درنظر گرفته می شود بشرطی که اضافه کاری از ۴ ساعت تجاوز نکند و به دو شرط زیر منوط شود:
- انجام اضافه کاری با توافق و رضایت کارگر باشد.
- پرداخت ۴۰% اضافه بر مزد هر ساعت کار عادی می باشد.
نحوه محاسبه ساعت اضافه کاری در اکسل:
درمورد محاسبه ساعت اضافه کاری پرسنل بنا به سیاست های هر شرکت یا کارگاهی می تواند متفاوت باشد که ما معمول ترین و ساده ترین آنرا لحاظ می کنیم.همانطور که گفتیم کارکرد پرسنل زمانی اضافه کاری تلقی می شود که حتی با اتمام ساعت پایان کار، در اختیار کارفرما باشد.پس ما باید ابتدا با تابع IF بررسی کنیم ببینیم که آیا پرسنل دیرتر از ساعت پایان کار خارج شده یا خیر.اگر از ساعت پایان کار گذشته باشد براحتی با تفریق ساعت خروج از ساعت پایان، ساعت اضافه کاری بدست می آید.
در سلول H5 فرمول زیر را نوشته و به سلول های پایینی درگ کنید( شکل شماره ۴):
(IF(E5>$D$2,E5-$D$2,0=
نحوه محاسبه میزان تاخیر پرسنل:
زمانی که پرسنل دیرتر از ساعت شروع کار در محل کار حاضر شود، از تفریق ساعت حضور با ساعت شروع کار میزان تاخیر پرسنل بدست می آید.توجه داشته باشید که در روزهای جمعه که کلا اضافه کاری محسوب می شود، تاخیر پرسنل معنایی ندارد.
پس در سلول G5 فرمول را مطابق شکل شماره ۵ نوشته و در سلول پایین درگ می کنیم:
محاسبه مبلغ اضافه کاری در اکسل:
مبلغ اضافه کاری براساس مبلغ هرساعت کارکرد عادی محاسبه می شود.پس برای بدست آوردن مبلغ اضافه کاری ابتدا باید مبلغ هرساعت کارکرد عادی را بدانیم.روش های مختلفی برای بدست آوردن وجود دارد که راحت ترین آن استفاده از جدول حقوق و مزایای ارائه شده توسط وزات کار می باشد.همچنین شما با دانستن یکی از موارد دستمزد مربوط به هرساعت کارکرد عادی، دستمزد روزانه یا حقوق ماهانه پرسنل می توانید بقیه آیتم ها را نیز محاسبه کنید.
طبق جدول حداقل حقوق و دستمزد کارگران قانون کار در سال ۹۸، حداقل دستمزد کارگران ( با مبنای ۳۰ روزه) مبلغ ۱۵،۱۶۸،۸۲۰ ریال می باشد.اکنون ما خیلی راحت می توانیم براساس مبلغ ماهانه، دستمزد روزانه و دستمزد هرساعت پرسنل را محاسبه کنیم:
دستمزد روزانه:
۵۰۵,۶۲۷= ۳۰ / ۱۵,۱۶۸,۸۲۰
دستمزد هرساعت کارکرد عادی:
۶۸,۹۸۰ = ۷.۳۳ / ۵۰۵,۶۲۷
پس دستمزد هرساعت کارکرد عادی پرسنل برابر با مبلغ ۶۸,۹۸۰ ریال می باشد.همانطور که در ابتدای این مطلب اشاره شد، برای محاسبه اضافه کاری ۴۰% مبلغ اضافه برمزد براساس مبلغ هرساعت کارکرد عادی پرداخت می شود که به ۴ روش زیر می توان نوشت:
۱* ۱۴۰% * ۶۸,۹۸۰
۱*۱.۴۰ * ۶۸,۹۸۰
۱*(( ۰.۴۰ * ۶۸,۹۸۰ ) + ۶۸,۹۸۰)
۱*(( ۴۰% * ۶۸,۹۸۰ ) + ۶۸,۹۸۰)
نتیجه هر ۴ فرمول بالا برابر ۹۶,۵۷۲ ریال خواهد بود که برای محاسبه یک ساعت اضافه کاری نوشته شده است .حال برای محاسبه مبلغ اضافه کاری ها از فرمول نهایی زیر باید استفاده شود:
جمع ساعت اضافه کاری پرسنل * ۱۴۰% * ۶۸,۹۸۰ = مبلغ اضافه کاری
فرمول محاسبه مبلغ اضافه کاری در اکسل:
رسیدیم به یکی از مهمترین و حساس ترین مرحله این آموزش یعنی محاسبه مبلغ اضافه کاری که اکثر کاربران اکسل با این موضوع مشکل اساسی دارند.اکثر کاربران برای محاسبه اضافه کاری در اکسل ساعت را در مبلغ ضرب می کنند و نتیجه ای که با آن مواجه می شوند اعدادی اشتباه و غیرمنطقی می باشد.چیزی که مسلم است این است که اعدادی که ما در ستون مربوط به اضافه کاری بدست آوردیم اعدادی با فرمت ساعت می باشد و این متفاوت تر از ارزش یک عدد عادی می باشد.کاری که ما باید انجام دهیم این است که ابتدا باید ساعات اضافه کاری را بر مبنای ۲۴ ببریم(در عدد ۲۴ ضرب کنیم) تا اعداد بدست آمده ساعتی بر مبنای یک روز کامل بدست بیاید.
ابتدا محدوده I5:I8 را انتخاب کرده سپس از تب Home و گروه Number، فرمت محدوده انتخابی را روی Number قرار دهید سپس در سلول I5 فرمول زیر را نوشته و به سلول های پایینی درگ کنید:
H5*24=
ما تا اینجا میزان اضافه کاری پرسنل را به اعداد معمولی تبدیل کرده ایم.اکنون خیلی راحت می توانیم با ضرب ساعت اضافه کاری بدست آمده در مبلغ، اضافه کاری پرسنل را محاسبه کنیم. در سلول I5 یکی از فرمول های زیر را نوشته و به سلول های پایینی درگ کنید:
H5*24*140%*68980=
H5*24*1.40*68980=
هردو فرمول بالا جواب یکسانی دارند و فرقی نمی کند از کدام استفاده کنید.
اگر می خواهید مبلغ ساعت کارکرد عادی پرسنل را بدست بیاورید همانند روش گفته شده برای مبلغ اضافه کاری عمل کنید با این تفاوت که از فرمول زیر برای مبلغ کارکرد عادی استفاده کنید:
جمع ساعت کارکرد عادی پرسنل * ۶۸,۹۸۰ = مبلغ کارکرد عادی
منبع:
درباره جاوید
عاشق اکسل هستم و بیش از 12 ساله دارم اکسل کار می کنم. در این مسیر آموزش ها و فایل های حرفه ای ارائه دادم و همچنان در این مسیر با علاقه پیش میرم چون امروزه اکسل به یک مهارت انکار نشدنی تبدیل شده
سایر نوشته های جاویدمطالب زیر را حتما مطالعه کنید
۴ گام تا شماره گذاری صفحه چاپ در اکسل با یک نکته عالی
ایجاد لیست شیت ها در ComboBox اکسل با دستورات VBA
در کمتر از ۱ دقیقه چک لیست کارهای روزانه در اکسل بساز
فایل اکسل محاسبه مالیات حقوق سال ۱۴۰۱ + آموزش ویدئویی فرمولنویسی مالیات
راست چین در اکسل و نحوه راست به چپ کردن شیت اکسل
برای چاپ اطلاعات داخل فرم از قبل پرینت شده در اکسل چکار کنیم؟
26 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
داداش توی ده تا مرورگر مختلف تست کردم و حتی رو گوشی هم همینطور و متاسفانه لینکی برای دانلود وجود نداره:( هرچی روی اون بخش کلیک میکنم اتفاقی نمیفته (یوزر آماتور نیستم و مطمعنم لینک دانلود کار نمیکنه) میشه لطفا لینک دانلود رو زیر همین کامنت رپلی کنین برام؟ تشکر
سلام داداش گلم لینک کار نمیکنه
سلام. وقت بخیر
بررسی شد. مشکلی در دانلود مشاهده نشد.
سلام خسته نباشید چطوری برای روز های جمعه ضریب ۲ در نظر بگیریم که هرچی اضافه کاری محاسبه شد *۲ بشود
سلام یه مسئله دارم که با استفاده از جدول بالا محسابه کنید تردد کارکنان چقدر میباشد. اگر تردد زیر ۹ ساعت بود و میزان تاخیر کمتر از ۲ ساعت بود نوشته شود مرخصی ساعتی اما اگر میزان تاخیر بیشتر از ۲ ساعت بود بزند مرخصی روزانه اگر در قسمت توضیحات جز افراد سفارش شده بود میزان تاخیر محاسبه نشود و کلمه عادی را نمایش دهد اگر میزان کاری بیشتر از ۹ ساعت کاری بود ، اضافه کاری محاسبه و نشان داد شود.
بافرمول ifکه میرم قسمت تاخیر رو انتخاب میکنم ومیزنم کمتر از۲ یابیشتر از ۲ بزنه مرخصی ساعتی یا روزانه ولی بازم مرخصی هارو نمیتونم درست دربیارم.
باسلام روز بخیر. برای پنجشنبه که ساعت کاری ۸ الی ۱ هست, باید چطوری حساب کنم؟ میشه ازتون خواهش کنم توضیح بدید.
سلام. وقت بخیر.
باید از IF تو در تو استفاده کتید.
داخل دوره آموزش محاسبه ساعت کارکرد پرسنل بصورت کامل آموزش دادیم.
سلام روز بخیر عذر می خوام برای پنج شنبه چطور باید حساب کنیم ساعت کارکرد پنجشنبه ۴ ساعت هست
سلام. وقت بخیر.
باید از IF تو در تو استفاده کتید.
داخل دوره محاسبه ساعت کارکرد پرسنل کامل آموزش دادیم.
سلام طوبی جان من رویام. برای کارکرد پنجشنبه که ۴ساعت هستش. چطوری حساب کردی؟ منم این مشکلو دارم
سلام روز بخیر عذر می خوام یه سوال داشتم
فرمول شکل شماره ۳ رو استفاده کردم ولی قسمت اول که جمعه و ۰ هست رو میگه اشتباهه
سلام. وقت بخیر
فرمول رو چجوری نوشتید؟
ممکنه اشتباه کرده باشید.
من در فایل اکسل سلول محاسبه ساعت اضافه کاری رو می خوام ضربدر نرخ ساعت اضافه کاری کنم، ولی جوابی که به من میده اشتباهه. در format cells کاری باید انجام شود؟
سلام.
ساعت*نرخ ساعت*۲۴
فرمت سلول رو روی Number تنظیم کنید.
مثلا اگر بخواییم بگیم توی هفته باید ۴۴ ساعت کار کرد.. و هر روز هفته ۸ ساعت ولی پنجشنبه ۴ ساعت.. اونطوری چی؟
سلام.
اینو میتونید توافق کنید
سلام
لینک دانلود این فایل متاسفانه نیستش انگاری؟!!!
ممنون میشم لینکش رو پیوست کنید باتشکر
سلام. وقت بخیر
فایل مربوطه آپلود شد.
میتونید دانلود کنید.
سلام وقت بخیر چطور میشه مثلا تایم۳۰دقیقه ناهار را از زمان کارکرد روزانه کسر کنیم؟
سلام. وقت بخیر
میتونید یک سلول رو به عنوان ساعت ناهار (۰۰:۳۰) درنظر بگیرید و طبق همین آموزش مقدار نهار رو از کارکرد کم کنید.
سلام فرمول محاسبه تاخیر و تعجیل واضافه کار ی و جمع ساعات اضافه کاری چیست؟
سلام. وقت بخیر
انتهای همین پست فایل آموزش رو دانلود کنید. داخل فایل فرمول محاسبه اضافه کاری و تاخیر نوشته شده.
یا میتونید از آموزش زیر استفاده کنید :
آموزش فرمول نویسی محاسبه ساعت کارکرد پرسنل
اگر ساعت ورود رو زودتر بزاریم یعنی در واقع تعجیل داشته باشیم اونوقت کارکرد عادی به مشکل میخوره اونوقت چکار کنیم
شما باید از تابع IF یا تابع IFS (در نسخه ۲۰۱۹ به بالا) استفاده کنید.
ما تو دوره فرمولنویسی محاسبه ساعت کارکرد اینرو کامل آموزش دادیم.
باسلام و خسته نباشید
سوالم در رابطه با محاسبه تأخیر هست
نحوه محاسبه هم تأخیر در ورود هم تعجیل در خروج چگونه است؟
ممنون و تشکر از سایت خوبتون
با سلام.
برای محاسبه تعجیل در فرم بالا می توانید از فرمول زیر استفاده کنید:
(IF(E5<$D$2,$D$2-E5,0=