golf4
08-28-2005, 04:49 PM
Hi, everyone -
I've hit the wall on this one, :banghead: :banghead: :banghead: , and was hoping to solicit some ideas on a formula problem within out Timesheet. Here is my current scenerio:
[1] I've constructed a data entry sheet where I have columns for each housing project, i.e. Project1, Project2, Project3, etc. Each employee enters the specific hours, each day, in the columns depending on how much time they work in the specific project. The last cell in each columns totals the hours worked in each column;
[2] There is also a seperate column for "Non-Project Hours", where employees enter hours spent each day on stuff like staff meetings, trainings, etc.: worked hours that cannot specifically be tied to any individual housing project;
[3] Sheet2 is, more or less, a Summary Sheet where it shows all the hours worked in each housing project, number of "Non-Project Hours", Total Hours Worked, etc.
All the formulas, on Sheet2, are working ok. The problem I have is when I attempt to demonstrate the % of hours worked compared to the TOTAL hours worked. What I'm looking for is to construct a fornula that would show:
A) Total Hours Worked (including OT column totals) in Project1 divided by Total Hours Worked. This was easy enough. For example, Employee A works 100 hours in Project1 out of a total of 200 TOTAL hours worked:50% of Employee A's time was spent in Project1.
B) The problem comes in when I try to include, in the calculation, the "Non-Specific Hours" into the percentage of hours. For example, Employee A spends 10 hours in staff meetings. Our Finance people are looking to include the breakdown of "Non-Specifc Hours" throughout the various housing projects by the specific project. So, Employee A works 100 hours in Project1 equalling 50% of time. Then what we'd look to do is to "spread" the Non-Specific Hours throughout each project, meaning 50% of the Non-Specific Hours would be tied to Project1. THIS IS MY PROBLEM. :dunno I've tried numerous ways, and can't seem to develop a formula that works the way on want it to.
Any help on this one would be FANTASTIC!!!!: pray2: : pray2: : pray2:
Thanks.
Frank
I've hit the wall on this one, :banghead: :banghead: :banghead: , and was hoping to solicit some ideas on a formula problem within out Timesheet. Here is my current scenerio:
[1] I've constructed a data entry sheet where I have columns for each housing project, i.e. Project1, Project2, Project3, etc. Each employee enters the specific hours, each day, in the columns depending on how much time they work in the specific project. The last cell in each columns totals the hours worked in each column;
[2] There is also a seperate column for "Non-Project Hours", where employees enter hours spent each day on stuff like staff meetings, trainings, etc.: worked hours that cannot specifically be tied to any individual housing project;
[3] Sheet2 is, more or less, a Summary Sheet where it shows all the hours worked in each housing project, number of "Non-Project Hours", Total Hours Worked, etc.
All the formulas, on Sheet2, are working ok. The problem I have is when I attempt to demonstrate the % of hours worked compared to the TOTAL hours worked. What I'm looking for is to construct a fornula that would show:
A) Total Hours Worked (including OT column totals) in Project1 divided by Total Hours Worked. This was easy enough. For example, Employee A works 100 hours in Project1 out of a total of 200 TOTAL hours worked:50% of Employee A's time was spent in Project1.
B) The problem comes in when I try to include, in the calculation, the "Non-Specific Hours" into the percentage of hours. For example, Employee A spends 10 hours in staff meetings. Our Finance people are looking to include the breakdown of "Non-Specifc Hours" throughout the various housing projects by the specific project. So, Employee A works 100 hours in Project1 equalling 50% of time. Then what we'd look to do is to "spread" the Non-Specific Hours throughout each project, meaning 50% of the Non-Specific Hours would be tied to Project1. THIS IS MY PROBLEM. :dunno I've tried numerous ways, and can't seem to develop a formula that works the way on want it to.
Any help on this one would be FANTASTIC!!!!: pray2: : pray2: : pray2:
Thanks.
Frank