Work out overtime hours for individuals

Hi All

I have a question which i am hoping someone will eb able to help me
with, i have a spreadsheet which contains infromation for part time
hours worked during the course of a month for individuals (e.g. below)

Name Start End Hours
Persona 1:30 2:30 1
Persona 3:30 4:30 1
Persona 9:30 11:30
person b
person b
person c
person c
person d etc etc

What i need is a formula that will allo wme to add an extra column
which gives me the total hours person a worked, person be worked and so
on. The original data contains multiple lines (i.e persona could have
10 lines, personb15 lines etc etc), and is used for temporay teachers
that may work different hours and days in a month.

any help is appreciate, and i hope the above makes sense

Hi!

Try this:

Assuming your range is A2:E6. Enter this formula in F2 and copy down as
needed:

=IF(A2=A3,"",SUMIF(A$2:A$6,A2,D$2:D$6))

Biff

"Fudge" <fudgie73@hotmail.com> wrote in message
news:1144534479.928455.51150@i39g2000cwa.googlegroups.com...
> Hi All
>
> I have a question which i am hoping someone will eb able to help me
> with, i have a spreadsheet which contains infromation for part time
> hours worked during the course of a month for individuals (e.g. below)
>
> Name Start End Hours
> Persona 1:30 2:30 1
> Persona 3:30 4:30 1
> Persona 9:30 11:30
> person b
> person b
> person c
> person c
> person d etc etc
>
> What i need is a formula that will allo wme to add an extra column
> which gives me the total hours person a worked, person be worked and so
> on. The original data contains multiple lines (i.e persona could have
> 10 lines, personb15 lines etc etc), and is used for temporay teachers
> that may work different hours and days in a month.
>
> any help is appreciate, and i hope the above makes sense
>

=SUMIF(A1A,"Person a",D:D)

If you need regular and overtime hours, just use

=MAX(40,SUMIF(A1A,"Person a",D:D))

for regular hours, and

=MIN(0,40-SUMIF(A1A,"Person a",D:D))

if regular hours are upto 40.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Fudge" <fudgie73@hotmail.com> wrote in message
news:1144534479.928455.51150@i39g2000cwa.googlegroups.com...
> Hi All
>
> I have a question which i am hoping someone will eb able to help me
> with, i have a spreadsheet which contains infromation for part time
> hours worked during the course of a month for individuals (e.g. below)
>
> Name Start End Hours
> Persona 1:30 2:30 1
> Persona 3:30 4:30 1
> Persona 9:30 11:30
> person b
> person b
> person c
> person c
> person d etc etc
>
> What i need is a formula that will allo wme to add an extra column
> which gives me the total hours person a worked, person be worked and so
> on. The original data contains multiple lines (i.e persona could have
> 10 lines, personb15 lines etc etc), and is used for temporay teachers
> that may work different hours and days in a month.
>
> any help is appreciate, and i hope the above makes sense
>

Hi thanks for the info, no i just need a column that gives me the total
hours worked for each individual as 1 line rather than me have to sit
there and calculate the total hours each for each record,

the other problem is will it calculate the hours if they are setup as
follow:

01:30
06:15
and so forth or do i need to convert the numerical fields first..as i
know in the past trying to get a sum doesnt always work.

Bob Phillips wrote:

> =SUMIF(A1A,"Person a",D:D)
>
> If you need regular and overtime hours, just use
>
> =MAX(40,SUMIF(A1A,"Person a",D:D))
>
> for regular hours, and
>
> =MIN(0,40-SUMIF(A1A,"Person a",D:D))
>
> if regular hours are upto 40.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Fudge" <fudgie73@hotmail.com> wrote in message
> news:1144534479.928455.51150@i39g2000cwa.googlegroups.com...
> > Hi All
> >
> > I have a question which i am hoping someone will eb able to help me
> > with, i have a spreadsheet which contains infromation for part time
> > hours worked during the course of a month for individuals (e.g. below)
> >
> > Name Start End Hours
> > Persona 1:30 2:30 1
> > Persona 3:30 4:30 1
> > Persona 9:30 11:30
> > person b
> > person b
> > person c
> > person c
> > person d etc etc
> >
> > What i need is a formula that will allo wme to add an extra column
> > which gives me the total hours person a worked, person be worked and so
> > on. The original data contains multiple lines (i.e persona could have
> > 10 lines, personb15 lines etc etc), and is used for temporay teachers
> > that may work different hours and days in a month.
> >
> > any help is appreciate, and i hope the above makes sense
> >

No, the formula I gave will sum up the hours, but format the cell as [h]:mm
to allow for more than 24 hours.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Fudge" <fudgie73@hotmail.com> wrote in message
news:1144586958.983431.251570@g10g2000cwb.googlegroups.com...
> Hi thanks for the info, no i just need a column that gives me the total
> hours worked for each individual as 1 line rather than me have to sit
> there and calculate the total hours each for each record,
>
> the other problem is will it calculate the hours if they are setup as
> follow:
>
> 01:30
> 06:15
> and so forth or do i need to convert the numerical fields first..as i
> know in the past trying to get a sum doesnt always work.
>
> Bob Phillips wrote:
>
> > =SUMIF(A1A,"Person a",D:D)
> >
> > If you need regular and overtime hours, just use
> >
> > =MAX(40,SUMIF(A1A,"Person a",D:D))
> >
> > for regular hours, and
> >
> > =MIN(0,40-SUMIF(A1A,"Person a",D:D))
> >
> > if regular hours are upto 40.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Fudge" <fudgie73@hotmail.com> wrote in message
> > news:1144534479.928455.51150@i39g2000cwa.googlegroups.com...
> > > Hi All
> > >
> > > I have a question which i am hoping someone will eb able to help me
> > > with, i have a spreadsheet which contains infromation for part time
> > > hours worked during the course of a month for individuals (e.g. below)
> > >
> > > Name Start End Hours
> > > Persona 1:30 2:30 1
> > > Persona 3:30 4:30 1
> > > Persona 9:30 11:30
> > > person b
> > > person b
> > > person c
> > > person c
> > > person d etc etc
> > >
> > > What i need is a formula that will allo wme to add an extra column
> > > which gives me the total hours person a worked, person be worked and
so
> > > on. The original data contains multiple lines (i.e persona could have
> > > 10 lines, personb15 lines etc etc), and is used for temporay teachers
> > > that may work different hours and days in a month.
> > >
> > > any help is appreciate, and i hope the above makes sense
> > >
>

thanks for that i will try it on the spread sheet tomorrow at work, and
willr evert back in the evening tomorrow if i encounter a problem.

Okay, will watch out for it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Fudge" <fudgie73@hotmail.com> wrote in message
news:1144603296.168169.209980@e56g2000cwe.googlegroups.com...
> thanks for that i will try it on the spread sheet tomorrow at work, and
> willr evert back in the evening tomorrow if i encounter a problem.
>

bob can this formula be written into a macro, so that on a monthly
basis when i print the list i can simply apply it to open into a new
work sheet with the figures as i need them? rather than having to write
out the formula every month

Fudge wrote:
> bob can this formula be written into a macro, so that on a monthly
> basis when i print the list i can simply apply it to open into a new
> work sheet with the figures as i need them? rather than having to write
> out the formula every month

Hi the fields that are used are as follow:

Staff`Ref Surname First NAme PT Worklog Start PT Worklog End Pay
Grade Cost Centre Ref Duration Hrly Rate (columns A-I) so column J
would contain the total for total hours worked for each individual
(multiple lines) what bits do i need to change on the formula, the
duration shows as 01:30, 00:30, 06:00

I've attached sample data from the worksheet for you to look at

Staff Reference Surname First Name PT Work Log Start Date PT Work Log
End Date Pay Grade Code Cost Centre Reference Duration Hourly Rate
1000872 Marks Janet 28/03/2006 28/03/2006 HP S000 02:00 23.56
1000872 Marks Janet 21/03/2006 21/03/2006 HP S000 02:00 23.56
1000872 Marks Janet 14/03/2006 14/03/2006 HP S000 02:00 23.56
1000872 Marks Janet 07/03/2006 07/03/2006 HP S000 02:00 23.56
1000872 Marks Janet 28/02/2006 28/02/2006 HP S000 02:00 23.56
1000872 Marks Janet 21/02/2006 21/02/2006 HP S000 02:00 23.56
1000647 Manning Clare 27/03/2006 27/03/2006 HP B300 00:30 23.56
1000647 Manning Clare 20/03/2006 20/03/2006 HP B300 00:30 23.56
1000647 Manning Clare 13/03/2006 13/03/2006 HP B300 00:30 23.56
1000647 Manning Clare 06/03/2006 06/03/2006 HP B300 00:30 23.56
1000647 Manning Clare 27/02/2006 27/02/2006 HP B300 00:30 23.56
1000647 Manning Clare 20/02/2006 20/02/2006 HP B300 00:30 23.56
1000463 Burton Lisa 31/03/2006 31/03/2006 HP B650 01:30 23.56
1000463 Burton Lisa 24/03/2006 24/03/2006 HP B650 01:30 23.56
1000463 Burton Lisa 17/03/2006 17/03/2006 HP B650 01:30 23.56
1000463 Burton Lisa 10/03/2006 10/03/2006 HP B650 01:30 23.56
1000463 Burton Lisa 03/03/2006 03/03/2006 HP B650 01:30 23.56
1000463 Burton Lisa 24/02/2006 24/02/2006 HP B650 01:30 23.56
1000315 Gunter Norman 06/03/2006 06/03/2006 HP T300 02:00 23.56

Thanks for the example, made it easy.

This is all the code you need.

Sub Test()
Dim cRows As Long

cRows = Cells(Rows.Count, "A").End(xlUp).Row - 1
Range("J2").Formula = "=SUMIF(A:A,A2,H:H)"
Range("J2").AutoFill Range("J2").Resize(cRows)

End Sub

If you add it to Personal.xls and add a button to a toolbar and assign it to
that button, it is always available.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Fudge" <FudgieM@gmail.com> wrote in message
news:1144659115.738049.283530@i39g2000cwa.googlegroups.com...
> I've attached sample data from the worksheet for you to look at
>
> Staff Reference Surname First Name PT Work Log Start Date PT Work Log
> End Date Pay Grade Code Cost Centre Reference Duration Hourly Rate
> 1000872 Marks Janet 28/03/2006 28/03/2006 HP S000 02:00 23.56
> 1000872 Marks Janet 21/03/2006 21/03/2006 HP S000 02:00 23.56
> 1000872 Marks Janet 14/03/2006 14/03/2006 HP S000 02:00 23.56
> 1000872 Marks Janet 07/03/2006 07/03/2006 HP S000 02:00 23.56
> 1000872 Marks Janet 28/02/2006 28/02/2006 HP S000 02:00 23.56
> 1000872 Marks Janet 21/02/2006 21/02/2006 HP S000 02:00 23.56
> 1000647 Manning Clare 27/03/2006 27/03/2006 HP B300 00:30 23.56
> 1000647 Manning Clare 20/03/2006 20/03/2006 HP B300 00:30 23.56
> 1000647 Manning Clare 13/03/2006 13/03/2006 HP B300 00:30 23.56
> 1000647 Manning Clare 06/03/2006 06/03/2006 HP B300 00:30 23.56
> 1000647 Manning Clare 27/02/2006 27/02/2006 HP B300 00:30 23.56
> 1000647 Manning Clare 20/02/2006 20/02/2006 HP B300 00:30 23.56
> 1000463 Burton Lisa 31/03/2006 31/03/2006 HP B650 01:30 23.56
> 1000463 Burton Lisa 24/03/2006 24/03/2006 HP B650 01:30 23.56
> 1000463 Burton Lisa 17/03/2006 17/03/2006 HP B650 01:30 23.56
> 1000463 Burton Lisa 10/03/2006 10/03/2006 HP B650 01:30 23.56
> 1000463 Burton Lisa 03/03/2006 03/03/2006 HP B650 01:30 23.56
> 1000463 Burton Lisa 24/02/2006 24/02/2006 HP B650 01:30 23.56
> 1000315 Gunter Norman 06/03/2006 06/03/2006 HP T300 02:00 23.56
>

as you probably can tell i am a total newbie at writing macros, how do
i add the macro to personal.xls and then make it available for everyone
to use, also do i have to write the code in visual basic.

The other thing i tried the formula u provided, it gives me th elines i
want, but then doesn't calculate some lines and leaves them as zero
despite there being hours.

If you want to share it with others, Personal.xls is no good. Why don't you
create a new workbook with the macro and a button, and save it as a template
file. Your users can then open that whenever they start afresh.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Fudge" <FudgieM@gmail.com> wrote in message
news:1144680107.549442.39100@i40g2000cwc.googlegroups.com...
> as you probably can tell i am a total newbie at writing macros, how do
> i add the macro to personal.xls and then make it available for everyone
> to use, also do i have to write the code in visual basic.
>
> The other thing i tried the formula u provided, it gives me th elines i
> want, but then doesn't calculate some lines and leaves them as zero
> despite there being hours.
>

column a = staff ref
column b = surname
column c = first name
column d = PT worklog start date
column e = PT worklog end date
column f = pay grade
column g = cost centre
column h = duration
column i = hourly rate

That is the setup of the above example, the duration field shows as
01:30 which i have formatted to [H]:mm. however when i drop the formula
in to column j row j2 it doesn't calculate the total hours for some
staff and shows as zero.