Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. In other words as below NAme Start End Hours DAte Total Person a 11:30 14:30 3 1/3/06 Person a 10:30 12:30 2 4/3/06 Person a 16:30 20:30 4 5/3/06 9 Person b 09:30 13:30 4 13/03/06 person b 08:30 11:30 3 01/03/06 7 and so on any help is appreciate, and i hope the above makes sense |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Work out overtime hours for individuals
Hi Fudge,
you can e.g. use the SUMIF function like: you type in a cell (somewhere next to the database, but not connecting to it <= practical advice or even in a summary spreadsheet) x2: persona y2: SUMIF(a:a;x2;d:d) x3: personb y3: SUMIF(a:a;x3;d:d) x4: personc y4: SUMIF(a:a;x3;d:d) <= function can be copied etc where a:a is the column with the names (MUST be the same with x2, x3, x4...) d:d is the column with the hours to be added and X2, x3, x4 ... are the teachers names, only once per person. this can be developed to more sophisticated tables :-) Any good? Best regards, ANdras (Hungary) "Fudge" wrote: 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. In other words as below NAme Start End Hours DAte Total Person a 11:30 14:30 3 1/3/06 Person a 10:30 12:30 2 4/3/06 Person a 16:30 20:30 4 5/3/06 9 Person b 09:30 13:30 4 13/03/06 person b 08:30 11:30 3 01/03/06 7 and so on any help is appreciate, and i hope the above makes sense |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Work out overtime hours for individuals
Hi Andras
thansk fro your response, but im not quite sure what you mean. ANdras wrote: Hi Fudge, you can e.g. use the SUMIF function like: you type in a cell (somewhere next to the database, but not connecting to it <= practical advice or even in a summary spreadsheet) x2: persona y2: SUMIF(a:a;x2;d:d) x3: personb y3: SUMIF(a:a;x3;d:d) x4: personc y4: SUMIF(a:a;x3;d:d) <= function can be copied etc where a:a is the column with the names (MUST be the same with x2, x3, x4...) d:d is the column with the hours to be added and X2, x3, x4 ... are the teachers names, only once per person. this can be developed to more sophisticated tables :-) Any good? Best regards, ANdras (Hungary) "Fudge" wrote: 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. In other words as below NAme Start End Hours DAte Total Person a 11:30 14:30 3 1/3/06 Person a 10:30 12:30 2 4/3/06 Person a 16:30 20:30 4 5/3/06 9 Person b 09:30 13:30 4 13/03/06 person b 08:30 11:30 3 01/03/06 7 and so on any help is appreciate, and i hope the above makes sense |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Work out overtime hours for individuals
Hi Fudge
Supposing your data is on Sheet1, with name in column A and Hours worked in column D. On sheet2, enter in column A a list of the individual names for which you wish to summarise the data. On sheet2 in cell B1 enter =SUMIF(Sheet1!A:A,A1,Sheet1!D:D) Copy the formula down column B for as many rows as you have entries in column A. -- Regards Roger Govier "Fudge" wrote in message oups.com... Hi Andras thansk fro your response, but im not quite sure what you mean. ANdras wrote: Hi Fudge, you can e.g. use the SUMIF function like: you type in a cell (somewhere next to the database, but not connecting to it <= practical advice or even in a summary spreadsheet) x2: persona y2: SUMIF(a:a;x2;d:d) x3: personb y3: SUMIF(a:a;x3;d:d) x4: personc y4: SUMIF(a:a;x3;d:d) <= function can be copied etc where a:a is the column with the names (MUST be the same with x2, x3, x4...) d:d is the column with the hours to be added and X2, x3, x4 ... are the teachers names, only once per person. this can be developed to more sophisticated tables :-) Any good? Best regards, ANdras (Hungary) "Fudge" wrote: 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. In other words as below NAme Start End Hours DAte Total Person a 11:30 14:30 3 1/3/06 Person a 10:30 12:30 2 4/3/06 Person a 16:30 20:30 4 5/3/06 9 Person b 09:30 13:30 4 13/03/06 person b 08:30 11:30 3 01/03/06 7 and so on any help is appreciate, and i hope the above makes sense |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Hours and overtime by week | Excel Worksheet Functions | |||
Time sheets | Excel Worksheet Functions | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
How can I work out how many hours I get premium rate? | Excel Worksheet Functions | |||
Work Hours Time Question | Excel Worksheet Functions |