قد يقف أمامك عقبة وهي انك تريد حساب عدد الساعات بين وقتين والتعامل معها في المعادلات مثلاً حساب ساعات العمل لموظف او عامل وقد تظن ان برنامج excel لن يستطيع القيام بعمل ذلك! .. ولكن في الحقيقة يُمكن لبرنامج excel بالتعامل مع الوقت كما يتعامل مع الارقام ايضاً ، لذلك سنتعرف في هذا الدرس عن كيف سنجعل برنامج excel يتعامل مع الوقت من خلال تنسيق الوقت في الخلايا و حساب ساعات العمل في excel وغيرها من الامور المتعلقة بهذا الأمر .
طريقة حساب ساعات العمل في Excel
مثلاً اذا كان امامنا هذا الجدول الموجود في الصورة التالية لحساب ساعات العمل لموظف اسمه بكر وهذا الموظف يعمل بدوام جزئي ، ويتم حسابه علي اساس ان الساعة ب 50 جنيهاً
اولاً : اذا كنت تسأل عن كيف قُمنا بعمل هذا الجدول يمكنك زيارة هذه الدرس :
كيفية عمل جدول على Excel وتنسيقه بالصور
ثانياً : وبعد ان قُمنا بعمل الجدول قُمنا بتغيير تنسيق التاريخ ..
ثالثاً : نقوم الآن بتعديل تنسيق الخلايا في عمود “الحضور” وعمود “الانصراف” لكي نُهيئ الخلايا للتعامل مع الوقت ( الساعة ) من خلال تنسيق الوقت والطريقة كالآتي :
1- قم بتظليل او تحديد الخلايا التي سيتم كتابة بها الوقت وفي حالتنا هذه تحديد خلايا عمود الحضور وعمود الانصراف
2- اضغط كليك يمين واختر format cell او تنسيق الخلايا
3- ومن النافذة التي ستظهر لك اختر قسم Number ومن ال category او الفئة اختر Time او الوقت ومن قسم type يمكنك اختيار الصيغة المناسبة لتنسيق الوقت ثم اضغط ok
الآن بعد تعديل تنسيق خلايا عمود”الحضور” وعمود “الانصراف” نقوم بكتابة ساعة الحضور وساعة الانصراف مع الإلتزام بهذه القواعد :
1- كتابة الساعة بالشكل التالي 8:00 اذا كان في تمام الساعة او كتابة عدد الدقائق مثلاً بهذا الشكل 8:20
2- اذا كانت الساعة مساءاً تُكتب بتنسيق الـ 24ساعة أي اذا كانت الرابعة مساءاً تكتب 16:00 .. رقم 16 لأن الساعة 4 مساءاً + 12 ساعة = 16 .. واذا كان الساعة 9 مساءاً تكتب 21:00 لأن 9 + 12ساعة = 21 وهكذا ..
لحساب عدد ساعات العمل الآن نقوم بكتابة المعادلة التالية :
=TEXT(E8-D8,“hh:mm”)
شرح المعادلة :
معادلة Text في هذه الحالة تقوم بتنسيق رقم وتحويله إلى صيغة الوقت hh:mm
خلية E8 هي ساعة الانصراف
خلية D8 هي ساعة الحضور
ووظيفة المعادلة انها ستقوم بطرح ( ساعة الانصراف – ساعة الحضور ) وتحويلها لصيغة الوقت وهي hh:mm
ونقوم بتكرار المعادلة في باقي خلايا عمود “عدد ساعات العمل”
ولكن قد تواجهنا مشكلة في حالات معينة وهي عندما يكون مدة عمل الموظف علي مدار يومين فمثلاً :
الموظف يأتي للعمل الساعة 10 مساءاً وينصرف 7 صباحاً اليوم التالي .. سيعطيك البرنامج في هذه الحالة رسالة خطأ value#
والسبب هو ان اكسل يعتبر ساعة الانصراف هذه اقل من ساعة الحضور _ حيث ان اكسل يقوم بتحويل الساعة الي قيمة رقمية بشكل ما _ وبالتالي لا يمكن الطرح لان النتيجة ستكون سالب _ وبالطبع لا يوجد وقت بالسالب _ لذلك يظهر لك خطأ value#
والحل هنا هو وضع 1+ بين اسماء الخلايا _ليتحول السالب الي موجب وتكون النتيجة صحيحة _
وستكون المعادلة بالشكل التالي :
=TEXT(E8+1-D8,“hh:mm”)
ولكن ايضاً هناك مشكلة مع هذه المعادلة حيث انها لا تصلح في الحالة الاولي عندما يكون مدة عمل الموظف على مدار يوم واحد
بالطبع هناك حل عزيزي القارئ فبرنامج excel لا يمكن ان يقف امامه عقبة ..
اذا فما هو الحل النهائي ؟!
يجب ان نستخدم معادلة تصلح مع الحالتين الاولي ( حالة ان مدة عمل الموظف على مدار يوم واحد ) والثانية ( حالة ان مدة عمل الموظف علي مدار يومين )
الحل هو استخدام دالة IF وستكون بهذا الشكل :
=IF(E8>=D8,TEXT(E8-D8,”hh:mm”),TEXT(E8+1-D8,”hh:mm”))
شرح المعادلة :
بكل بساطة فهذه المعادلة دمج بين معادلة الحالة الاولى ومعادلة الحالة الثانية .. حيث ان إذا كانت ساعة الانصراف اكبر من او تساوي ساعة الحضور _ وهي حالة ان مدة عمل الموظف علي مدار يوم واحد _ يتم استخدام معادلة الحالة الاولي واذا كان غير ذلك سيقوم تلقائياً بإستخدام معادلة الحالة الثانية
حساب أجر عدد ساعات العمل
وبعد ان قُمنا بحساب عدد ساعات العمل للموظف نريد الآن حساب أجر هذه الساعات .. والطريقة كالاتي :
1- نقوم بإضافة عمود لإجمالي الاجر وتم تسميته ب “القيمة”
2- سنستخدم في عمود القيمة هذه المعادلة :
=F8*G8*24
شرح المعادلة :
هي عملية ضرب عدد ساعات الحضور F8 في اجر الساعة G8 في 24
و 24 هنا تشير إلي 24 ساعة
حساب ساعات العمل الاضافي في Excel
واذا كان هذا الموظف له عدد ساعات معين واذا زاد عنه يتم حساب له اجر مختلف ..
في هذه الحالة
عدد الساعات الاضافية = اجمالي ساعات الحضور – ساعات العمل الأساسية
ولطريقة حساب ساعات العمل الاضافي سنقوم بعمل الاتي :
1- عمود خاص بعدد ساعات العمل الاضافي
2- وقمنا بعمل خلية لساعات العمل الأساسي وهي الخلية J5
بإفتراض ان عدد ساعات العمل الاساسية هي 5 ساعات
قُمنا بتغيير تنسيق الخلية الي هذا التنسيق المُشار اليه في الصورة التالية
وكتابة عدد الساعات العمل الاساسية بهذا الشكل 5:00:00 hh:mm:ss ساعات : دقائق : ثواني
3- سنستخدم لحساب عدد ساعات العمل الاضافي هذه المعادلة في عمود عدد ساعات العمل الاضافي :
=MAX(F8-$J$5,0)
عناصر المعادلة هي :
دالة max
F8 = عدد ساعات الحضور
J5 = عدد ساعات العمل الاساسية
0 = رقم ثابت
شرح المعادلة :
معادلة Max وهي المسؤولة عن ايجاد ايهم ذو قيمة اكبر
وفي حالتنا هذه القيمة الاولي هي ( نتيجة طرح عدد ساعات الحضور F8 – عدد ساعات العمل الاساسية J5 = ساعات العمل الإضافية) اما القيمة الثانية وهي ( الصفر )..
حيث اذا كان عدد ساعات العمل الإضافية = صفر يعطي النتيجة صفر
واذا كانت سالبة عندما تكون عدد ساعات الحضور اقل من ساعات العمل الاساسية تكون النتيجة صفر ايضاً لأن الصفر اكبر من اي رقم سالب
واذا كان غير ذلك يعطينا عدد ساعات العمل الاضافية الفعلية لانه بالطبع سيكون اكبر من الصفر
4- عمل عمود خاص بأجر ساعات العمل الاضافي
5- تعديل معادلة عمود القيمة الي هذا الشكل لتقوم بحساب ساعات العمل الاضافي بشكل صحيح ..
حيث ان في هذه الحالة
قيمة اجر الموظف = (عدد ساعات العمل الاساسية * أجرها) + (عدد ساعات العمل الاضافية * أجرها)
=(F8-G8)*H8*24+(G8*I8*24)
شرح المعادلة :
F8 = عدد ساعات الحضور
G8 = عدد ساعات العمل الاضافي
H8 = اجر الساعة الطبيعي
I8 = اجر ساعات العمل الاضافي
اي انها = (عدد ساعات الحضور- عدد الساعات الاضافية ) * اجر الساعة الطبيعي + ( ساعات العمل الاضافي * اجر ساعات العمل الاضافي )
حساب ساعات العمل الاضافي
اجمالي عدد ساعات العمل
لإيجاد اجمالي ساعات العمل للموظف .. سنقوم بعمل الآتي :
1- كتابة هذه المعادلة التالية اسفل الجدول في عمود عدد ساعات العمل / الحضور
=TEXT(F8+F9+F10+F11+F12+F13+F14,”hh:mm“)
شرح المعادلة :
كما قلنا سابقاً ان معادلة Text في هذه الحالة تقوم بتنسيق رقم وتحويله إلى صيغة الوقت hh:mm
حيث ان F8 F9 F10 F11 F12 F13 F14 هي خلايا عمود عدد ساعات العمل
الطريقة الوحيدة المتاحة هي جمع الخلايا يدوياً ( فلن تصلح معادلة sum في هذه الحالة )
موضوعات قد تُهمك ايضاً :
م عربى صلاح
يتم تبديل العلامة , الى العلامة :