Working time formula

JohnBoy1995 

I found a good site that taught me that rules. I don't have to deal with overtime but here is their example below:

Calculating working time when you have taken leave

You will need to make up time in your calculation if you are away during the reference period because you are taking:

• paid annual leave
• maternity leave
• paternity leave
• adoption leave
• parental leave
• time off sick

You do this by extending the reference period by the same number of days as you were away. You also look at the hours you worked on the days which were immediately before the 17-week period.

Example two

You have a standard working week of 40 hours (eight hours a day). You then do overtime of eight hours a week for the first 12 weeks of your 17-week reference period. You also take four days annual leave and work one normal day (eight hours) that week. When you return to work, you only do your normal hours with no overtime for one week.

• step one: add together the 16 weeks of normal hours, plus one day normal hours, plus the 12 weeks of overtime (16 x 40) + (1 x 😎 + (12 x 😎 = 744 hours during the reference period
• step two: include the time worked on the four days directly after the 17-week period 4 x 8 = 32 + 744 = 776
• step three: 776 hours should be divided by 17 (the number of weeks in the reference period) 776 ÷ 17 = 45.6

This means you would have worked an average of 45.6 hours per week, which would be within the working time limits

JohnBoy1995 

Perhaps

=SUMIFS(R2:R127,Q2:Q127,"<>Leave")

JohnBoy1995 

Help us help you by uploading a sample of the actual worksheet that you have so far. Otherwise, we'd need to create our own, if only to test any recommendation.