I'm entering formulas into a timesheet. Rows 8 thru 14 are for the days of the week. Column I reflects the total hours for each row. Column J reflects the regular hours for each row. Column O reflects overtime for each row in excess of 8 hours regular time reflected in Column J.
I need a formula on Friday's and Saturday's row that will reflect only regular hours not to exceed 40 during the week. In rows 8 thru 12, Column J, I have the formula =FLOOR(MIN($I8,8/24),15/24/60), and in Column O I have the formula =IF(I8<=J8,0,FLOOR(I8-J8+0.0000001,15/24/60)). This works well for each day until Friday. If an employee works Sunday thru Thursday, 8 regular hours a day, then any time worked Friday and Saturday wouldn't be reflected in Column J (regular hours), but should be in Column O (overtime). On the other hand, if an employee works regular hours only Monday thru Friday, I need Friday's row to include the last 8 hours (of a total of 40) in Column J.
I don't know how to modify my formula to produce this result. Can anyone help?