calculating overtime hours with including week off/Holidays for the operatives!

[Mohammad Munawar]

Required a formula help for calculating overtime hours for the operatives with the following below steps.


1. In a column H (Header Normal Hours) only return a hours between 0.1 to 3.5 after calculating the headers Finish time (Column G) to start time (Column H), whatever exceeded other than 3.5 to be return in column I (Additional/Fridays/Holidays).

2. The Fridays and holidays all working hours always to be return in a column I (Additional/Fridays/Holidays).

3. The paid amount to be return in a Column K by calculating hours (Normal and Holidays) with different rates mentioned in cells M3:M4.



Thanks for the help,

[JeteMc]

The following yields the expected results:
1. For cell H7 and down: =IF(A7="","",IF(OR(WEEKDAY(A7)=6,B7=M$2),0,MIN((G7-F7)*24,3.5)))
2. For cell I7 and down: =IF(A7="","",(G7-F7)*24-H7)
3. For cell K7 and down: =IF(A7="","",SUM(H7*M$3,I7*M$4))
Let us know if you have any questions.

[Mohammad Munawar]

Many thanks JeteMc for your solution.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.