Resource planning from date periods

LisaB:-)

Hi everyone, I have a problem which I'm really stuck on.

On about a dozen schedules, there are hundreds of rows, assigned to 'ID's' (ID1, ID2 or ID3) that have durations from 1 to 9 days.

I need to be able to predict resource capacity for those IDs, up to 6 months in advance, by week. In this example, the ID starts the task on 7 June and ends on 17 June, so there will be 4 days w/c 6 June, and 5 days w/c 13 June.

I have created a report, grouped and summarised, but that shows the whole 9 day period in Wk 23 (w/c 6 June), which won't be accurate for resourcing purposes, because it will suggest we need 2 IDs for Wk 23, but 0 for Wk 24, when in fact, we will need 1 ID for Wks 23-24.

Any suggestions would be most gratefully received.

Julio S.

Hi @LisaB:-) ,

If my understanding is correct, you are attempting to automatically assign tasks to ID, ID2 and ID3 on the "Assign to" column based on the number of weeks a task will take. I.e for tasks lasting 1 week, ID needs assigned; for 2 weeks ID2 and for 3 weeks and over ID3.

If this is the case, you may achieve what you intend by creating an additional Column to count on the number of weeks a task will occupy and apply the following formulas in these Columns:

• On Number of Weeks: =IF(WEEKNUMBER(Start@row) <> WEEKNUMBER(Finish@row), WEEKNUMBER(Finish@row) - WEEKNUMBER(Start@row) + 1, 1)
• On Assigned To: =IF([Number of weeks]@row >= 3, "Email address for ID3", IF([Number of weeks]@row = 2, "Email address for ID2", "Email address for ID"))

As a note, if the assignments don't need to be for specific emails, you may consider changing the "Assign to" Column to a Dropdown single select, otherwise you may need to convert the formula results into valid contacts after the assignation occurs automatically.

If this is not quite what you are trying to achieve, please expand on what is the intended outcome of each task assignation and wether or not the relevant IDs are specific email addresses that need to be contacts in your project.

I hope this can provide with some insights about what you are trying to achieve.

Cheers!

 Julio

LisaB:-)

Hi Julio, thanks for responding.

No, that's not what I'm trying to achieve. The IDs need to be assigned manually to the rows.

This is a screenshot of the how we're currently manipulating the data in excel, manually. We need to know how many IDs are needed for each week.

The problem arises, as in my OP. I have created a report, grouped and summarised, but that shows the whole 9 day period in Wk 23 (w/c 6 June), which won't be accurate for resourcing purposes, because it will suggest we need 2 IDs for Wk 23, but 0 for Wk 24, when in fact, we will need 1 ID for Wks 23-24.

Does that help at all?

Many thanks, Lisa

Julio S.

Thanks for the clarification @LisaB:-) ,

If you need to be more granular about each week number a task is worked on, you may want to include sub-tasks for each task worked on different weeks, this will create a roll-up with the whole of the task (you may leave the weeknumber as 0 in the parent to avoid confusion in your reporting). This way your duration will be counted for on a week by week basis.

Sheet layout:

Resulting Report:

I hope this can be more clarifying about what you are trying to achieve.

Cheers!

Julio

LisaB:-)

Hi Julio, thanks for the suggestion. Unfortunately, we can't add further subtasks in to the schedules, as this will make setup and execution more labour intensive - there are thousands of rows on some of these schedules these tasks repeat up to a hundred times.

I can't think of a way to do this, because Ss doesn't 'know' what weeks are. I may have to export the report into excel and then manipulate the data there.