Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time sheet calculations
Excel 2003 - hope this is the right group to ask.
I am trying to find a formula for a time sheet for employees. I will try to show an example: Col A (job number) Col B hours in day 2103x 4.0 2402 2.0 1234 2.0 ___ sub-total 8.0 This continues in four blocks for each working day of the month. At the end of block 4, I need a total of all the sub-totals. The person who set up the sheet just had: =SUM(C14+C23+C32+C41+C50+C59+C67+C75+G14+G23+ G32+G41+G50+ G59+G67+G75+ K14+K23+K32+K41+K50+K59+K67+K75+ O14+O23+O32+O41+O50+O59+O67 etc, but I'm sure there must be an easier way!! I would really appreciate some advice - I have tried using Help but don't really know how to phrase the question! TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time sheet calculations
You might want to reformat the sheet using the subtotal() function
check help for details The subtotals would thewn truely be sutotals and the master total would not count the subtotals shown in other words if C14 is =subtotal(9,C6:C13) and C23 =subtotal(9,C15:C22) etc and in c68 you wanted a true total =subtotal(9,C6:C67) would be the total ignoring all of the subtotal in the range. in other words your final equation might be something like =subtotal(9,C6:C67,G6:G67,K6:K67,O6:O67) as long as all of the current subtotals were made using subtotal() "ab" wrote: Excel 2003 - hope this is the right group to ask. I am trying to find a formula for a time sheet for employees. I will try to show an example: Col A (job number) Col B hours in day 2103x 4.0 2402 2.0 1234 2.0 ___ sub-total 8.0 This continues in four blocks for each working day of the month. At the end of block 4, I need a total of all the sub-totals. The person who set up the sheet just had: =SUM(C14+C23+C32+C41+C50+C59+C67+C75+G14+G23+ G32+G41+G50+ G59+G67+G75+ K14+K23+K32+K41+K50+K59+K67+K75+ O14+O23+O32+O41+O50+O59+O67 etc, but I'm sure there must be an easier way!! I would really appreciate some advice - I have tried using Help but don't really know how to phrase the question! TIA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time sheet calculations
Try...
=SUMPRODUCT(--(MOD(ROW(C14:C75)-ROW(C14),9)=0),(C14:C75+G14:G75+K14:K75+O 14:O75)) If you have more columns, let's say Column C to Column Z, try the following instead... =SUM(IF(MOD(ROW(C14:C75)-ROW(C14),9)=0,IF(MOD(COLUMN(C14:Z14)-COLUMN(C14) ,4)=0,C14:Z75))) This formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "ab" wrote: Excel 2003 - hope this is the right group to ask. I am trying to find a formula for a time sheet for employees. I will try to show an example: Col A (job number) Col B hours in day 2103x 4.0 2402 2.0 1234 2.0 ___ sub-total 8.0 This continues in four blocks for each working day of the month. At the end of block 4, I need a total of all the sub-totals. The person who set up the sheet just had: =SUM(C14+C23+C32+C41+C50+C59+C67+C75+G14+G23+ G32+G41+G50+ G59+G67+G75+ K14+K23+K32+K41+K50+K59+K67+K75+ O14+O23+O32+O41+O50+O59+O67 etc, but I'm sure there must be an easier way!! I would really appreciate some advice - I have tried using Help but don't really know how to phrase the question! TIA |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time sheet calculations
Thanks so much - that's just what I need.
I'm probably pushing it a bit but on the same worksheet - further down so on Page 2 - there is a two-column summary sheet so that we can see how many hours have been spent on a particular job during the month. At present, I have to go through Page 1 and pick out all hours spent on, say, Job 2000 and enter a total in the summary followed by all other jobs worked on: 2000 44 1234 86 etc. The sum at the end of this table *should* equal the Total on the Final Equation on the first sheet! Is it possible to create a formula for this - with so many jobs over the month, I sometimes miss something the first time and have to go over it all again! "bj" wrote in message ... You might want to reformat the sheet using the subtotal() function check help for details The subtotals would thewn truely be sutotals and the master total would not count the subtotals shown in other words if C14 is =subtotal(9,C6:C13) and C23 =subtotal(9,C15:C22) etc and in c68 you wanted a true total =subtotal(9,C6:C67) would be the total ignoring all of the subtotal in the range. in other words your final equation might be something like =subtotal(9,C6:C67,G6:G67,K6:K67,O6:O67) as long as all of the current subtotals were made using subtotal() "ab" wrote: Excel 2003 - hope this is the right group to ask. I am trying to find a formula for a time sheet for employees. I will try to show an example: Col A (job number) Col B hours in day 2103x 4.0 2402 2.0 1234 2.0 ___ sub-total 8.0 This continues in four blocks for each working day of the month. At the end of block 4, I need a total of all the sub-totals. The person who set up the sheet just had: =SUM(C14+C23+C32+C41+C50+C59+C67+C75+G14+G23+ G32+G41+G50+ G59+G67+G75+ K14+K23+K32+K41+K50+K59+K67+K75+ O14+O23+O32+O41+O50+O59+O67 etc, but I'm sure there must be an easier way!! I would really appreciate some advice - I have tried using Help but don't really know how to phrase the question! TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detailed Time Sheet (overtime, comp time, vacation used) | New Users to Excel | |||
More than calculations in time | Excel Discussion (Misc queries) | |||
Time calculations and additional 24 hour days | Excel Worksheet Functions | |||
Time stamp in Excel Sheet | Excel Discussion (Misc queries) | |||
How to set up weekly time sheet with flexi time. | Excel Worksheet Functions |