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.
>