I have spent hours using all sorts of formulas and searching through excel forums but am still to find the solution. It seems to be simple, it seems I'm really close but it still does not give me the correct results. What I need is to cap the number of hours at 40 as soon as it's reached and move the remaining hours, if any, to the row below and on the day the cap was reached. Our employees have flexible schedule and are not required to work a minimum 8 hours a day, so sometimes they work less and sometime choose to come on weekends. In the example below row 23 shows the actual hours worked and in rows 24 & 25 I have formulas to separate regular time from the overtime. The 40 hours was reached on Friday at 4.5hr mark and the rest is overtime. All good so far:
The problem starts when there is a gap after the cap was reached, i.e. using the same example, the person reached the cap on Friday, did not work on Saturday but came to work on Sunday. This is what I get:
which is clearly incorrect - I have not enough regular hours and too many overtime hours. Here is another example:
Appreciate your help!
The problem starts when there is a gap after the cap was reached, i.e. using the same example, the person reached the cap on Friday, did not work on Saturday but came to work on Sunday. This is what I get:
which is clearly incorrect - I have not enough regular hours and too many overtime hours. Here is another example:
Appreciate your help!
