محاسبه اضافه کاری و ساعت تاخیر در اکسل+دانلود فایل

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

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

لیست ساعت ورود و خروج پرسنل
شکل شماره 1 – لیست ساعت ورود و خروج پرسنل

تعریف ساعت کار:

طبق ماده 51 قانون كار، ساعت كار مدت زماني است كه كارگر نيرو يا وقت خود را به منظور انجام كار در اختيار كارفرما قرار مي دهد. ساعات صرف صبحانه و نهار جزء ساعت کار به حساب نمی‌آید.

نحوه بدست آوردن ساعت کار موظفی:

ساعت کار موظفی روزانه طبق قانون کار بصورت هفتگی محاسبه می شود.در مشاغل عادی(بجز مشاغل سخت و زیان آور) ساعت کار باید 44 ساعت در هفته به ازای 6 روز باشد.یعنی اگر ما 44 ساعت را در 6 تقسیم کنیم مقدار …7.3333333 ساعت یا همان 7 ساعت و 20 دقیقه (7:20) بدست می آید که همان ساعت کاری روزانه می باشد.یعنی ساعت کار عادی هر شخص روزانه 7 ساعت و 20 دقیقه می باشد ونباید از 8 ساعت در روز تجاوز کند.

محاسبه ساعت کار موظفی در اکسل:

همانطور که در شکل شماره 1 مشاهده می کنید ما سلول F2 را به عنوان ساعت شروع کار(8:00) و سلول D2 را به عنوان ساعت پایان کار(16:00) درنظر گرفته ایم تا براحتی قابل تغییر باشد.حال می خواهیم در سلول B2 ساعت کار موظفی کارگر را در فرم فوق محاسبه کنیم.فرمول زیر را در سلول B2 بنویسید:

محاسبه ساعت کارکرد عادی در اکسل:

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

حتما بخوانید:   ساعت در اکسل و روش های محاسبه کارکرد پرسنل

پس برای محاسبه ساعت کارکرد عادی در سلول F5 فرمول زیر را نوشته و به سلول های پایینی درگ کنید. توجه داشته باشید که در فرمول زیر فرض شده است که ساعت ورود پرسنل حتما باید از ساعت 8:00 به بعد باشد و حضور زودتر پرسنل از موعد مقرر به هیچ عنوان اضافه کاری محاسبه نمی شود.

محاسبه ساعت کارکرد عادی پرسنل
شکل شماره 2 – محاسبه ساعت کارکرد عادی پرسنل

نقصی که در فرمول فوق وجود دارد این است که کارکرد روزهای جمعه را نیز بعنوان کارکرد عادی درنظر می گیرد.درحالی که کل ساعات کاری در روز جمعه باید اضافه کاری تلقی شود.پس فرمول را مطابق شکل شماره 3 اصلاح می کنیم:

محاسبه ساعت اضافه کاری جمعه در اکسل
شکل شماره 3 – محاسبه ساعت اضافه کاری جمعه در اکسل

تعریف اضافه کاری:

درصورتی که ساعات کاری کارگر خارج از ساعت کاری تعریف شده باشد، مازاد آن اضافه کاری درنظر گرفته می شود بشرطی که اضافه کاری از 4 ساعت تجاوز نکند و به دو شرط زیر منوط شود:

  • انجام اضافه کاری با توافق و رضایت کارگر باشد.
  • پرداخت 40% اضافه بر مزد هر ساعت كار عادی می باشد.

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

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

در سلول H5 فرمول زیر را نوشته و به سلول های پایینی درگ کنید( شکل شماره 4):

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

مثل فرمول محاسبه اضافه کاری که در بالا نوشتیم تا اگر روز کاری جمعه بود تمام ساعت کاری را به عنوان اضافه کاری پرسنل محاسبه کند.پس فرمول H5 را به شکل زیر اصلاح می کنیم:

محاسبه ساعت جمعه کاری در اکسل
شکل شماره 5 – محاسبه ساعت جمعه کاری در اکسل

نحوه محاسبه میزان تاخیر پرسنل:

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

پس در سلول G5 فرمول را مطابق شکل شماره 6 نوشته و در سلول پایین درگ می کنیم:

محاسبه ساعت تاخیر پرسنل در اکسل
شکل شماره 6 – محاسبه ساعت تاخیر پرسنل در اکسل

محاسبه مبلغ اضافه کاری در اکسل:

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

طبق جدول حداقل حقوق و دستمزد کارگران قانون کار در سال ۹۸، حداقل دستمزد کارگران ( با مبنای ۳۰ روزه) مبلغ ۱۵،۱۶۸،۸۲۰ ریال می باشد.اکنون ما خیلی راحت می توانیم براساس مبلغ ماهانه، دستمزد روزانه و دستمزد هرساعت پرسنل را محاسبه کنیم:


دستمزد روزانه:

505,627= 30 / ۱۵,۱۶۸,۸۲۰

دستمزد هرساعت کارکرد عادی:

68,980 = 7.33 / 505,627

پس دستمزد هرساعت کارکرد عادی پرسنل برابر با مبلغ 68,980 ریال می باشد.همانطور که در ابتدای این مطلب اشاره شد، برای محاسبه اضافه کاری 40% مبلغ اضافه برمزد براساس مبلغ هرساعت کارکرد عادی پرداخت می شود که به 4 روش زیر می توان نوشت:


1* 140% * 68,980

1*1.40 * 68,980

1*(( 0.40 * 68,980 ) + 68,980)

1*(( 40% * 68,980 ) + 68,980)

نتیجه هر 4 فرمول بالا برابر 96,572 ریال خواهد بود که برای محاسبه یک ساعت اضافه کاری نوشته شده است .حال برای محاسبه مبلغ اضافه کاری ها از فرمول نهایی زیر باید استفاده شود:

حتما بخوانید:   فرم نظرسنجی در اکسل با دکمه های رادیویی

فرمول محاسبه مبلغ اضافه کاری در اکسل:

رسیدیم به یکی از مهمترین و حساس ترین مرحله این آموزش یعنی محاسبه مبلغ اضافه کاری که اکثر کاربران اکسل با این موضوع مشکل اساسی دارند.اکثر کاربران برای محاسبه اضافه کاری در اکسل ساعت را در مبلغ ضرب می کنند و نتیجه ای که با آن مواجه می شوند اعدادی اشتباه و غیرمنطقی می باشد.چیزی که مسلم است این است که اعدادی که ما در ستون مربوط به اضافه کاری بدست آوردیم اعدادی با فرمت ساعت می باشد و این متفاوت تر از ارزش یک عدد عادی می باشد.کاری که ما باید انجام دهیم این است که ابتدا باید ساعات اضافه کاری را بر مبنای 24 ببریم(در عدد 24 ضرب کنیم) تا اعداد بدست آمده ساعتی بر مبنای یک روز کامل بدست بیاید.

ابتدا محدوده I5:I8 را انتخاب کرده سپس از تب Home و گروه Number، فرمت محدوده انتخابی را روی Number قرار دهید سپس در سلول I5 فرمول زیر را نوشته و به سلول های پایینی درگ کنید:

بدست آوردن ساعت اضافه کاری بر مبنای 24 ساعت
شکل شماره 7 – بدست آوردن ساعت اضافه کاری بر مبنای 24 ساعت

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

هردو فرمول بالا جواب یکسانی دارند و فرقی نمی کند از کدام استفاده کنید.

محاسبه مبلغ اضافه کاری پرسنل در اکسل
شکل شماره 8 – محاسبه مبلغ اضافه کاری پرسنل در اکسل

اگر می خواهید مبلغ ساعت کارکرد عادی پرسنل را بدست بیاورید همانند روش گفته شده برای مبلغ اضافه کاری عمل کنید با این تفاوت که از فرمول زیر برای مبلغ کارکرد عادی استفاده کنید:


دانلود فرم محاسبه کارکرد:


منبع:

https://www.extendoffice.com

اشتراک گذاری:
مطالب زیر را حتما بخوانید

15 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

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

    • با سلام.

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

      (IF(E5<$D$2,$D$2-E5,0=

  • با سلام و خسته نباشید من یه فرمول میخوام برای زمانی که ساعت کاری پرسنل نا متعارفه مثلا 19 امروز تا 8 صبح فردا از اونجا که شیفت شب 22 تا 6 هست من چطور ساعات رو از هم تفکیک کنم

    • سلام.
      برای محاسبه ساعت بین دو روز مختلف، فرض کنيد سلول A1 ساعت ورود و سلول B1 ساعت خروج باشد.از فرمول زير براي محاسبه ساعت کارکرد استفاده کنيد سپس فرمت سلولي که فرمول را داخل آن نوشتيد را روي Time قرار دهيد.

      نکته: کوتیشن های قبل و بعد علامت کوچکتر در فرمول را پاک کنید.چون فرمول بهمریخته نشون میداد مجبورم شدم کوتیشن اضافه کنم.

      (IF(A1′<'B1,B1-A1,(1-A1)+B1=

  • سلام خسته نباشید شیفت کاری من از ۱۰ شب تا ۷ صبح هستش میشه لطف کنید بگید چجوری درست کنم یا یک روز رو تعریف کنید اکسلی برام ایمیل کنید ممنون میشم استاد

  • با سلام و خسته نباشید چطور میتونم ساعت تاخیر را محاسبه کنم مثلا ساعت 8:30 را چطور به صورت عدد منفی در
    ارم مثلا 30-

    • سلام… وقت بخیر

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

  • سلام چرا وقتی این فرمول رو وارد میکنم این خطا رو میده؟ =IF((E5-D5)>$B$2,$D$2-D5,E5-D5)
    وقتی علامت , میزنم خطا میده
    The Formula you typed contains an error
    دنباله جمله دیگه زیاد بود ننوشتم

      • ممنونم مشکل حل شد ولی یه مشکل دیگه هست من نمیخوام ساعت شروع و پایان کار داشته باشم ملاک واسه شرکت 8 ساعت کارکرده اگر کمتر باشه غیبت رد میشه اگر بیشتر اضافه کاری ما 8ساعت طبق حقوق ماهانه میگیریم یعنی ساعتی 5500 و اضافه کاری ساعتی 7500 واسش چه فرمولی بزنم بهتره؟

        • شما باید برای هر سه نوع ساعت یعنی کارکرد عادی،اضافه کار و تاخیر(همان غیبت شما) ایجاد کنید.در یک سلول خالی ساعت کارکرد روزانه یعنی همان 8:00 را وارد کنید و فرمول رو بصورت زیر بنویسید:

          در ستون کارکرد عادی : اگر ساعت خروج منهای ورود بزرگتر-مساوی ساعت کارکرد عادی بود، مقدار 8:00 را نشان دهد
          در ستون اضافه کاری :اگر ساعت خروج منهای ورود بزرگتر از ساعت کارکرد عادی بود، ساعت کارکرد آن روز منهای ساعت کارکرد موظفی شود.
          در ستون تاخیر : اگر ساعت خروج منهای ورود کوچکتر از ساعت کارکرد عادی بود، ساعت کارکرد موظفی منهای ساعت کارکرد آن روز شود.

          • سلام جناب مختاری ممنون از حمایت و آموزش های شما.
            راستش هرکاری میکنم با اون چیزی که من میخوام جور در نمیاد نمیشه یه فیلم آموزشی یا یه توضیح بهتر واسم بزارین ممنون میشم خیلی نیاز دارم به خدا.میدونید من از این فرمول ها این رو میخوام:
            واسه من اینها ملاکه
            ستون های من: ماه.روز.ورود.خروج.ورود.خروج.کارکرد عادی.تاخیر.اضافه کاری.کارکرد خالص.مبلغ کارکرد آن روز…
            حالا در باره کار شرکت شرکت(بسته بندی میگو) ما در طول سال (به غیر از دو کراپ حقوق ماهانه ساعتی 5500 میگیریم)دو کراپ داریم که در کراپ اول برج 4و5 صبح تا ظهر ودر کراپ دوم در برج 8و9 ظهر تا شب یا صبح میباشد.تو این کراپ دوم که الان هستیم بعضی وقتها ممکنه ساعت 9 صبح بیام و 14 بریم و دوباره 18 بیام و 3صبح بریم به خاطر همین دوبار ستون ورود و خروج رو ایجاد کردم حالا در زمان عادی که حقوق ماهانه میگیریم اگر کمتر از 8ساعت کار کنیم به همان مقدار واسمون تاخیر میزنن یعنی اگر ساعت ورود 7:30 باشه و خروج 15:30 ولی ما 8بیام و 15 بریم واسمون تاخیر میزنن واسه شرکت ملاک 8ساعت کاره نه کمتر و اگر بیشتر از 8ساعت کار کنیم اضافه کاری داریم(ساعتی 7500) روز جمعه هم اگر کار کنیم اضافه کاری حساب میشه اگر هم نه مرخصی.ولی وقتی کراپ شروع بشه اگر کمتر از 8ساعت کار کنیم همان 8ساعت واسمون حساب میکنن و اگر بیشتر کار کنیم اضافه کاری داریم.حالا میشه من به جای اینکه ساعت ورود و خروج رو ملاک فرمول قرار بدم فقط کارکرد موضفی رو ملاک فرمول قرار بدم(یا هر جور که به نظر شما بهتره انجام بدم)یعنی واسه ستون کارکرد عادی این فرمول رو بده: که اگر از 8ساعت کمتر بود تو همون ستون محاسبه کنه و اگر از 8 ساعت بیشتر بود 8ساعت اول رو تو ستون کارکرد عادی و الباقی رو اضافه کاری و اگر تاخیری هم بود تو ستون تاخیر بنویسه و تو ستون مبلغ کارکرد آن روز 8ساعت اول رو در 5500 و الباقی رو در 7500 محاسبه کنه. واگر هم در ستون های تاخیر و اضافه کاری چیزی نبود ساعت صفر رو نشون بده.ممنوم میشم کمکم کنید خدا هفظتون کنه
            اگر هم امکان ارسال فایل اکسل هست تا واستون چیزی که درست کردم رو بفرستم تا شما واسم اصلاحش کنید ممنون از حمایتتون…

پاسخی بگذارید