Dear All,
As I yet to get solution on my trial to calculate the overtime, I am bothering you again. There are several conditions for calculation, only before 8:30 and 17:00 hours over time is counted for. Further on Friday, Saturday and on other national holidays there will be no over hover hours but are paid on a fixed rate. To make the Fridays and Saturdays I also put in column “G” conditional format to mark the cell dark, it works somehow but when ever I type some thing in any cell of column “G” it also takes the dark colour. One more problem if I delete the date in Column “A” then the corresponding cell of column “G” becomes dark also. Moreover I see the error in calculating time.
Say $G$5= 8:30, $I$5=17:00
B8= 8:30 and C8=18:00, then overtime should be 1.00 hour but the formula =IF(OR(WEEKDAY(A8)=6,WEEKDAY(A8)=7),0,IF(OR(ISBLANK(B8),ISBLANK(C8)),"",INT(IF(OR(B8>G$5,C8<I$5),0,($G$5-B8+($G$5<B8))+(C8-$I$5+(C8<$I$5)))*24))) gives 0.00
I upload the find for your necessary corrections.
Kindest regards,
Subhash
Bookmarks