Forum Discussion
Working time formula
Hi,
I need to write a formula to work out the average working time for staff over a 17 week(119 days) period. But if they use 'leave' then I must discount this day and add on the next working day.
Column R= Number of hours worked
Column Q= indicates a leave day
Formula I have so far that doesn't work
'=(SUM(R2:(R127+(COUNTIF(Q2:Q127,'Leave'))))/119)*7'
Any help will be greatly appreciated
10 Replies
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 In my 35 years in Finance, with close relations to HR, I have never come across such theoretic calculation of working hours. But that's irrelevant. I tried to apply the calculation rules to your data. Created some extra columns (based on 40 hours per week / 8 hours per day) sorted your table with the most recent date first.
For every day, I first look at the week de day is in. Then, count the number of "leave" days in the past 16 weeks. Then the norm for 16 weeks at 40hr/wk and the leave days at 8hr/day. Then I sum these and divide by 17. Th number in column J gives you the rolling average for that particular day, 17 x 7 days into the past. If you are only interested in whole weeks, you could perhaps filter only Mondays in column B.
As said, I'm totally alien to these type of calculations, but perhaps they help you in achieving your objectives.
Thank you, but that won't add on the extra time.
So if they have 5 days leave in that period then it will be 17 weeks + 5 more daysSergeiBaklan
JohnBoy1995 Perhaps I'm over-simplifying, but if you enter 0 (zero) hours on a Leave day, you can use =AVERAGE(<range>).
For example, if a person worked 4 days of 8 hours and took leave 1 day, the average will become (8+8+8+8+0)/5 = 6.4hrs per day. Multiply by 5 (or 7) and you have the average number of hours per week.
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.
Does this help? mathetes
