time sheet formula

[ianm34]

hi there i am trying to build a calculator for the UK working time directive it requires workers to work no more than 48 hrs a week averaged over a 17 week period as drivers hours vary from day-day week - week i have made a weekly time sheet style spreadsheet which is fine until i get to

if one week they work 56 hrs = 8 hrs above 48 hrs
next week 48 hrs so that is equal to the weekly
3rd week 42 hrs so they have worked less than 48 hrs
etc etc
the week starts sun finishes sat if a day is not used for any reason hol sick etc the value is counted as 00:00 as i have done the hrs as a time formula

eg wk 1 total hrs worked 56 = 8 hrs above 48 so 48 -56 = 8
but what if hrs are less 42 hrs worked so 48-42 = +6
i can do the formula one way or the other but not both

i'm ok with basic formulas but this has me stumped
thaks in advance if anyone can help

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[ianm34]

thanks Roy
I wasn't sure whether to do that or not
As you can see from what ive done (its not all finished yet) but the bit im stuck on is
C19 = L5 minus L6 L7 that bit i have done
as you can see i have subtracted C19 from A 19 =C 20 which means i am below the 48 average week
but if i have a week that C19 is higher than A19 how can i do that so the formula works both plus or minus

[royUK]

maybe

=IF(C19>A19,C19-A19,A19-C19)

[ianm34]

Many thanks Roy
A true gentleman much appreciated
once again thanks

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save