Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workload spreadsheet - pivot tables
Wonding if anyone can help? I'm stumped!
I'm trying to create a spreadsheet to help with workload. Each job is assigned to one person and is given a start date and number of days to complete. I then calculate start date as the max of the date the project was entered & the last day that the person was available. (i.e. can't start on a new job until the previous one is finished). The end date is then calculated as the start date + workday(number of days). So far so good! I then want to pivot by person to add up how many hours they have in each week, to allow balancing of workload. I have a pivot table grouped by the job start date (grouped by week), which I've made a pretty graph of (makes it easy to see who is over / under-utilised). I'm not sure how to deal with projects which are longer than a week, or cross over a week boundary. At the moment they are all summing on the pivot table to the week in which they start, but that means that there may be 80 hours shown in one week and none in the next week. I think it would be much easier to use, if it "level-ised" across weeks. I think it would be possible to write a macro, but I'm trying to not use them if possible (the end users aren't familiar with coding). I thinkk array formulae may do the trick, but I'm not sure how to implent. I can upload my spreadsheet somewhere, if someone tells me where / how! Thanks, Kate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workload spreadsheet - pivot tables
I think your sum needs to be adjusted so that projects taking more than a
week are "trimmed". If( EndDate Monday+4 , 40, counthours) kind of thing? "KateB" wrote in message ... Wonding if anyone can help? I'm stumped! I'm trying to create a spreadsheet to help with workload. Each job is assigned to one person and is given a start date and number of days to complete. I then calculate start date as the max of the date the project was entered & the last day that the person was available. (i.e. can't start on a new job until the previous one is finished). The end date is then calculated as the start date + workday(number of days). So far so good! I then want to pivot by person to add up how many hours they have in each week, to allow balancing of workload. I have a pivot table grouped by the job start date (grouped by week), which I've made a pretty graph of (makes it easy to see who is over / under-utilised). I'm not sure how to deal with projects which are longer than a week, or cross over a week boundary. At the moment they are all summing on the pivot table to the week in which they start, but that means that there may be 80 hours shown in one week and none in the next week. I think it would be much easier to use, if it "level-ised" across weeks. I think it would be possible to write a macro, but I'm trying to not use them if possible (the end users aren't familiar with coding). I thinkk array formulae may do the trick, but I'm not sure how to implent. I can upload my spreadsheet somewhere, if someone tells me where / how! Thanks, Kate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workload spreadsheet - pivot tables
I think that's close - that will fix the problem in week 1, but how do
I get it to insert the carryover hours in week 2? Thanks for your help! Much appreciated Kate On Jul 21, 5:19*am, "Patrick Molloy" wrote: I think your sum needs to be adjusted so that projects taking more than a week are "trimmed". If( EndDate Monday+4 , 40, counthours) kind of thing? "KateB" wrote in message ... Wonding if anyone can help? *I'm stumped! I'm trying to create a spreadsheet to help with workload. *Each job is assigned to one person and is given a start date and number of days to complete. *I then calculate start date as the max of the date the project was entered & the last day that the person was available. (i.e. can't start on a new job until the previous one is finished). The end date is then calculated as the start date + workday(number of days). *So far so good! I then want to pivot by person to add up how many hours they have in each week, to allow balancing of workload. *I have a pivot table grouped by the job start date (grouped by week), which I've made a pretty graph of (makes it easy to see who is over / under-utilised). I'm not sure how to deal with projects which are longer than a week, or cross over a week boundary. *At the moment they are all summing on the pivot table to the week in which they start, but that means that there may be 80 hours shown in one week and none in the next week. *I think it would be much easier to use, if it "level-ised" across weeks. I think it would be possible to write a macro, but I'm trying to not use them if possible (the end users aren't familiar with coding). *I thinkk array formulae may do the trick, but I'm not sure how to implent. *I can upload my spreadsheet somewhere, if someone tells me where / how! Thanks, Kate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workload spreadsheet - pivot tables
if the data is in week2 then deduct what came earlier
IF(week=2, hours-40, hours) "KateB" wrote in message ... I think that's close - that will fix the problem in week 1, but how do I get it to insert the carryover hours in week 2? Thanks for your help! Much appreciated Kate On Jul 21, 5:19 am, "Patrick Molloy" wrote: I think your sum needs to be adjusted so that projects taking more than a week are "trimmed". If( EndDate Monday+4 , 40, counthours) kind of thing? "KateB" wrote in message ... Wonding if anyone can help? I'm stumped! I'm trying to create a spreadsheet to help with workload. Each job is assigned to one person and is given a start date and number of days to complete. I then calculate start date as the max of the date the project was entered & the last day that the person was available. (i.e. can't start on a new job until the previous one is finished). The end date is then calculated as the start date + workday(number of days). So far so good! I then want to pivot by person to add up how many hours they have in each week, to allow balancing of workload. I have a pivot table grouped by the job start date (grouped by week), which I've made a pretty graph of (makes it easy to see who is over / under-utilised). I'm not sure how to deal with projects which are longer than a week, or cross over a week boundary. At the moment they are all summing on the pivot table to the week in which they start, but that means that there may be 80 hours shown in one week and none in the next week. I think it would be much easier to use, if it "level-ised" across weeks. I think it would be possible to write a macro, but I'm trying to not use them if possible (the end users aren't familiar with coding). I thinkk array formulae may do the trick, but I'm not sure how to implent. I can upload my spreadsheet somewhere, if someone tells me where / how! Thanks, Kate |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workload spreadsheet - pivot tables
i'm not sure if we're on the same page - are you talking about doing
this with a macro? Or additional columns? I can see how you calculate the residual number of hours, but I'm not sure how you get this to insert as another row for the pivot data. I think you need another row with the date of the next week, so the pivot will properly summarise the workload. Thanks again, Kate On Jul 21, 8:05*am, "Patrick Molloy" wrote: if the data is in week2 then deduct what came earlier IF(week=2, hours-40, hours) "KateB" wrote in message ... I think that's close - that will fix the problem in week 1, but how do I get it to insert the carryover hours in week 2? Thanks for your help! *Much appreciated Kate On Jul 21, 5:19 am, "Patrick Molloy" wrote: I think your sum needs to be adjusted so that projects taking more than a week are "trimmed". If( EndDate Monday+4 , 40, counthours) kind of thing? "KateB" wrote in message .... Wonding if anyone can help? *I'm stumped! I'm trying to create a spreadsheet to help with workload. *Each job is assigned to one person and is given a start date and number of days to complete. *I then calculate start date as the max of the date the project was entered & the last day that the person was available. (i.e. can't start on a new job until the previous one is finished). The end date is then calculated as the start date + workday(number of days). *So far so good! I then want to pivot by person to add up how many hours they have in each week, to allow balancing of workload. *I have a pivot table grouped by the job start date (grouped by week), which I've made a pretty graph of (makes it easy to see who is over / under-utilised). I'm not sure how to deal with projects which are longer than a week, or cross over a week boundary. *At the moment they are all summing on the pivot table to the week in which they start, but that means that there may be 80 hours shown in one week and none in the next week. *I think it would be much easier to use, if it "level-ised" across weeks. I think it would be possible to write a macro, but I'm trying to not use them if possible (the end users aren't familiar with coding). *I thinkk array formulae may do the trick, but I'm not sure how to implent. *I can upload my spreadsheet somewhere, if someone tells me where / how! Thanks, Kate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables across multiple data in spreadsheet | Excel Discussion (Misc queries) | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Any way to programmatically make pivot tables behave more like data tables? | Excel Programming | |||
(Tom?) Pivot tables, code to refer to all pivot tables on template | Excel Programming | |||
Pivot tables-controlling user interaction with pivot tables | Excel Programming |