Suggestions for formula for calculating time allocation


Board Regular
Sep 13, 2006
Hi All

This is a long one/complicated one... (for me anyway!)

I have been producing a spreadsheet to show resource availability for people on work projects. i.e. what they are doing with their time, when will they become free for more work.

I have been helped in previous posts to work out the formulas used for this. (thanks!).

Now i have a list of names and projects, and time they are spending on the project within a particular month.

Now i want to report on:
a) how many people are working on the project in a particular month
b) the percentage of time spent on the project compared to time spent on other projects.

I would suggest i need to
a) create a calculation/formula of no of employees divided by number on a particular project. (word specific?)
b) create a calculation/formula of time per individual working on that project compared to others times NOT working on the project.

I know it sounds complicated and i hope i have explained it sufficiently to get some responses..!?

Thanks very much


Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
It would be helpful ( for me anyway) if you could provide some data to work with and what you have already.

My first thoughts are that a pivot table might do what you are after
Upvote 0
Hi GorD

Thanks for responding.

I am at work at the moment, so am unable to send a copy of the spreadsheet ( i can do this- this evening..)

I am also unable to download the software to enable me to upload the sheet from work.

Is there any sort of data i could provide via this HTML box..? (for now..)

Upvote 0
Ok, ive managed to produce an example of data...

Name Project ID Project Task SEP OCT NOV

Mark A123456 Finance Analysis 0.5 0.6 0.2
Tim B543212 Marketing Analysis 1 1 1
John ZZZZZZZ Sales Program 0.6 0.2 0.1
Harry A123456 Finance Analysis 0.5 0.6 0.3
Jimbob B543212 Marketing Analysis 1 1 1
Gary A123456 Finance Analysis 0.6 0.2 0.4

0.5 represents 50% of his time allocated, where 1 represents 100%.
Another worksheet will therefore state that in september Mark is free for 50% of that month- as he is only allocated to 0.5. And Tim will be unavailable SEP-NOV as he is allocated 100%.

For example project A123456 is being worked on by 3 people.
Firstly i want to identify- in a formula- how many people are working on A123456.
Secondly, i want to identify what percentage of resource has been allocated to that project compared to the other projects.(i.e.for SEP 0.5,0.5,0.6 compared to 1, 1 and 0.6 for other projects). So i can say "20% of our resource currently is allocated to project A123456"

Upvote 0
Not sure if this is going to help much, but if you can turn your data into a list format, rather than the tabular style.
Also it would be easier if everybodies time was allocated including non contract time.
1NameContractJob TypeMonthHrs
2MarkA123456Finance AnalysisSep-060.5
3TimB543212Marketing AnalysisSep-061
4JohnZZZZZZZSales ProgramSep-060.6
5HarryA123456Finance AnalysisSep-060.5
6JimbobB543212Marketing AnalysisSep-061
7GaryA123456Finance AnalysisSep-060.6
8MarkA123456Finance AnalysisOct-060.6
9TimB543212Marketing AnalysisOct-061
10JohnZZZZZZZSales ProgramOct-060.2
11HarryA123456Finance AnalysisOct-060.6
12JimbobB543212Marketing AnalysisOct-061
13GaryA123456Finance AnalysisOct-060.2
14MarkA123456Finance AnalysisNov-060.2
15TimB543212Marketing AnalysisNov-061
16JohnZZZZZZZSales ProgramNov-060.1
17HarryA123456Finance AnalysisNov-060.3
18JimbobB543212Marketing AnalysisNov-061
19GaryA123456Finance AnalysisNov-060.4

See next post for pivot tables
Upvote 0
You can then use pivot tables to summarise how you like
21Contract View -(you can take the names out if want)
22Count of HrsMonth
23ContractNameSep-06Oct-06Nov-06Grand Total
27A123456 Total3339
30B543212 Total2226
32ZZZZZZZ Total1113
33Grand Total66618
35This would show FREE TIME
36Sum of HrsMonth
45Sum of HrsMonth
46ContractSep-06Oct-06Nov-06Grand Total
50Grand Total100.00%100.00%100.00%100.00%

Only problem seems to be that not all the time is accounted for so the %ages are not what you might need.
Upvote 0
Thanks for doing this...
I will look at it in more depth when i am back in work on Tuesday.

Cheers for all your effort.
Upvote 0

I have had more time to look at this now- and it seems ideal!

I assume i will have to download HTMLMaker2.42 to view the formulas will i?

Upvote 0
No ,there are no formulas - it's all done with pivot tables.

Unfortunately, I think I will have overwritten the spreadsheet I created by now.

But if you have other questions fire away, and I or someone else on the board will do their best to answer.
Upvote 0
Hi GorD (or anyone)

Can you please confirm that i need to download the HTML Maker?
I cant see where i need to dl it from...!/ help... again!

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back