weekly time calculation

[Dave Elenor]

I am want to calculate how many hrs have been worked over a fornight basics
i.e.

A1 14:50
A2 13:30
A3 10:45
A4 12:50
A5 10:25
A6 15:00
A7 13:50
A8 12:10
A9 9:50
A10 10:55
A11 8:40
A12 13:00
A13 12:00
A14 10:40

I am a HGV driver and need to keep an account on my hours

Also I need to know how to get a average of my weekly hours work over a 17
wks period

A1 69 hrs
A2 74 hrs

and so on up to 17 weeks

Their is a new law coming out, for HGV drivers which means we cant work more
than 48 hours a week.
This can be averaged out over a 17 wk period. does anybody know if there is
a calculation I can use to work out the average over the 17 wks
I am thinking of working my normal hrs then taking a week or what ever I
need to take

 

[Sandy Mann]

Hi Dave,

It all depends on exactly what you want. I mean you say 48 hours averaged
over 17 weeks, does that mean that you only want to calculate it in whole
weeks?

Assuming that you don't, this is what I came up with hoping that it will
give
you some ideas.

17 weeks @ 48 hours is 816 hours so I labelled Columns A:C Date, Day & Hours
respectively.

Enter the start date in A2 and the formula: =TEXT(A2,"dddd") in B2.

Then in D122 (you need 119 rows of data to start with) enter the formula:

=INT(SUM(OFFSET(C122,-118,0):C122)*24-816)&" Hours "&
ROUND(MOD((SUM(OFFSET(C122,-118,0):C122)*24-816),1)*60,0)&"minutes"

(watch out for the line wrap & remove the carrage return in the formula bar
if you copy and paste)

in E122 I put the formula:

=IF(SUM(OFFSET(C122,-118,0):C122)*24-816>0,"Over Your Time","")

Copy all the fomulas down as far as you want using the fill handle and when
you enter the hours data the number of hours you are over or
under, (as a negative), will be returned in column D and will stop at the
last time entry and the returns in column E telling you that you are over
your time will continue on until the you are no longer have too many driving
hours regardless of whether or not there is a time in column C so you can
see how many days you can have off.

The 1900 date system can calculate negative times but it cannot show them,
that is what the *24 is for and note that you need Saturdays and Sundays to
be displayed even if you do not put any entery in them, (SUM will ignore
blank cells)

HTH

Sandy

 

[Sandy Mann]

Hi Dave,

What I posted before was full of bugs. try this

A1:E1 label with: Date, Day, Hours Worked, Hours, Over/Under Your Target
respectively.

Format Column A as Date, Colimn C as Time h:mm, Column D as Custom [h]:mm
and leave the rest as General

Enter the start date in A2 and copy down using the fill handle to any
desired row. In B2 enter
=TEXT(A2,"dddd") and copy down, (double clicking on the fill handle will
automatically copy down as far as you copied Column A)

In D2 enter:
=IF(SUM($C$2:C2)>48/7*(ROW()-1)/24,SUM($C$2:C2)-COUNT($A$2:A2)*48/7/24,"")
and in E2 enter:
=IF(SUM($C$2:C2)>48/7*(ROW()-1)/24,"Over Your Time","") and copy both down
to Row 119


In D120 enter:
=IF(ISNUMBER(C120),ABS(SUM(OFFSET(C120,-118,0):C120)-34),"")
In E120 enter:
=IF(SUM(OFFSET(C120,-118,0):C120)>34,"Over Your
Time",IF(SUM(OFFSET(C120,-118,0):C120)=34,"On
Target",IF(ISNUMBER(D120),"Under Your Time",""))) and copy both down as far
as Column A.

If you want me to send an example sheet send me an e-mail

Regards

Sandy