Resource Schedule solution

[wire10]

Hi Everyone,

I am trying to build a resourcing schedule and coming to a bit of roadblock in deriving a solution.

Following is the data fields I am working with and require a solution to derive an output to allow only available capacity for any particular resource to be scheduled show further work for already schedule date.

Resource Name	Start Date	End Date	Effort required %	Available Capacity %
Jack	4/04/2024​	4/06/2024​	0.25​	0.75​
Sam	4/04/2024​	4/06/2024​	0.75​	0.25​
Tanya	5/05/2024​	4/07/2024​	1​	0​

for example, Jack has 75% capacity available to be utilised for the scheduled duration between 4th April to 4th June therefore, i want to have a formula to allow only the available capacity to allocate for any other work otherwise there should be an alert either the column should turn into red or have a dialog box appear with alert "over capacity".

Is this a possibility?

Your help is greatly appreciated.

Cheers

 

[awoohaw]

How do you record the schedule? Do you have a template for that already? What has determined that Jack has 75% available? - Do you have schedule data somewhere? Why isn't that made available here?

Thanks in advance! and welcome to the forum.

 

[wire10]

How do you record the schedule? Do you have a template for that already? What has determined that Jack has 75% available? - Do you have schedule data somewhere? Why isn't that made available here?

Thanks in advance! and welcome to the forum.

Hi Awoohaw, thankyou for responding so quickly.

currently it's all manually added data with some calculation done to calculate networkdays and deriving capacity at any given time.

I am slowly building it up, but my first thing is to review and alert user of a resources being overbooked (above 100%) based on the current data.

hope it make sense.

 

[awoohaw]

Okay,
How do you plan on recording the schedule, will you have a flat file of just two columns and names and days scheduled that will feed into a dashboard and control table for how dates are managed (similar to what you have above)?(my preference for any data management, and easier). Or some other way?

What is your definition of 1)effort required and 2)capacity in terms of how a day or time period is worked? Or is it just remaining time to work that month.
Are weekends and holidays, or even scheduled vacations to be considered?

 

[wire10]

Okay,
How do you plan on recording the schedule, will you have a flat file of just two columns and names and days scheduled that will feed into a dashboard and control table for how dates are managed (similar to what you have above)?(my preference for any data management, and easier). Or some other way?

What is your definition of 1)effort required and 2)capacity in terms of how a day or time period is worked? Or is it just remaining time to work that month.
Are weekends and holidays, or even scheduled vacations to be considered?

To your first point, yes, will be managing it manually for better control. I like to automate but data is too messy.

to your second point - effort required is captured as per EFT i.e 1 = 100%, 0.5 = 50% and so forth. day are network days so no weekend or holidays to be added and hours worked are 8 hours/day.

The effort is captured as number of days x 8 x effort required. Similarly, Available Capacity is captured as Total Capacity (number of days x 8) - Effort captured/ Total Capacity.

I want to use the Available Capacity to run against the new entry for a resource and alert the user if it is over 100% where the dates are overlapping to what a resource is already committed to already.

Example, John has submitted an effort of 50% from 24/04/2024 to 30/06/2024 for project A. For Project B, we want to allocate John however, Project B requires 75% effort for the same period (or part of) John is already committed. This should generate an alert for the admin team to avoid booking John on Project B due to his available capacity.

Hope it helps.

 

[awoohaw]

This seems like you're asking to have a model built for you. The forum can help with many things, but it is all volunteer. Maybe some forum members know how to do this and will chime in.

Mr. Excel has consulting services that may be able to commit the time necessary for this.
Please look at this link: Consulting Services