PDA

View Full Version : [SOLVED] Challenging Formula Question on Timesheet Calcs



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

MWE
08-28-2005, 05:29 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 have done several appls like this spreading non-project labor back over funded projects. Can you post your spreadsheet so that I can see how you have set things up. Are you looking for an embedded solution (formulas in cells) or a VBA solution (calcs in the background)?

Bob Phillips
08-29-2005, 05:27 AM
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.

Should be simple too


=ROUND(project_hours+(project_hours/total_hours*non_specific_hours),2)

golf4
08-29-2005, 10:11 PM
Thanks to everyone. Our "crack" Finance people came up with the same formula that xld came up with. WHAT CAN I SAY...... I was never a math whiz!!! Isn't that why we have Excel?????? Now I just have to formulate the formulas for each project.
Thanks again to everyone. :beerchug: :beerchug: :beerchug:

Take care.

Frank