Resource Management - Formula for various different schedules

Jez Akali

Hi all,

CC:@Genevieve P.

I'm using smartsheet for resource management and the sheet links to our smartsheet resource tool.

In RM smartsheet, we have different resources on different schedules e.g:

• Some work 7 hours a day
• Some work part time (4 hours a day)
• Some work 4 days a week at 8 hours and 7 hours (Fridays off)

We have been using a set formula to determine the allocation % of reach resource which is incorrect as it doesn't take into account of the different scenarios and people have been complaining that the planned hours that RM smartsheet is suggesting is wrong, as they should be.

=Hours@row / (Duration@row * 8). - This assumes everyone is full time and working 8 hours a day.

The sheet below is what I use to push the resources through to RM smartsheet. We're trying to figure the best way to set each project up so it takes into account of the different above scenarios, and that the allocation will be accurate, which means RM smartsheets will suggest the current planned hours

Does anybody have any suggestions please?

Smartsheet

Example of planned hours in RM smartsheet.

Thanks!

Jez

Mark Ryan

Hi @Jez Akali ,

I have a slightly scenario working. We have different resources globally who have different contracted work hours in a day. I have a separate sheet that is derived from our HR team that tells me for each resource in the system what their contracted hours per day is.

Then in my main sheet I do a lookup against the Assigned To column, that then populates into a hidden field so that the work hours per day is then shown for that employee. The % Allocation is then calculated with the following formula that calculates based on the duration of the task, and the planned effort the task is due to consume.

=IF(OR(ISBLANK([Resource Type]@row), Duration@row = 0), "", IF(NOT(ISBLANK([Resource Type]@row)), [Planned Work (h)]@row / (Duration@row * [Work hours per day]@row), ""))

This than calculates an accurate % Allocation for a resource, based on the number of hours a resource works in a day, so Resource Management calculates the same number.

Hopefully I've managed to explain myself there, but let me know if you want any further clarification.

Thanks,

Mark

Jez Akali

Hi Mark,

Thank you for reaching out!

Is it possible to attach some screenshots please? Appreciate the sensitivity of the data, test data will be helpful.

Do you have any instances where some of your resources only work Monday to Thursday?

Thanks!

Jez

Mark Ryan

Hi @Jez Akali,

I'll pull together some screenshots to try and explain it better. In the meantime, I forgot to mention in Resource Management, the default work day is set to 8 hours, but for employee's that don't work the default I edit the user profile and use the Availability section to specify the hours that person works:

Using the same configuration, I do have one employee that works a 4 day week, so in the settings above I leave Friday set to 0. This is more for when the resource reports their time, that we then don't expect a value to be reported on a Friday.

I'll try and get some screenshots tomorrow,

Thanks,

Mark

Mark Ryan

Hi @Jez Akali,

Here is the technical explanation

I have a static sheet that contains the basic HR information. For the purpose of the screenshots, I have filtered the sheet and hidden rows, but the important fields used in this workflow are shown below

In my project planning sheet, I have a column that basically does a lookup of the Assigned To column, to work out what a working day consists of, which is then used to calculate the % Allocation based on the planned hours and the Duration

The formula that does this calculation is:

=INDEX({CS Team HR Setup | Contract hours per day}, MATCH([Assigned To]@row, {CS Team HR Setup | User}, 0))

The % Allocation column is then calculated as follows:

=IF(ISBLANK([Assigned To]@row), "", [Planned Work (hours)]@row / (Duration@row * [Work hours per day]@row))

LikeI I mentioned above, in Resource Management, I then specify for each user their availability (if it's different from the standard 8 hours Monday to Friday), so for me the setup is as follows:

When the Smartsheet syncs the data to Resource Management, the hours in Resource Management now match the planned hours that were specified in the project plan

Hopefully that all makes sense.

Thanks,

Mark

Jez Akali

Thank you @Mark Ryan that makes perfect sense.

Those that work 4 days a week, and have Friday off seem to be throwing my project hours off in RM smartsheet.

In my Smartsheet for example:

I have assigned a task to a user that has Friday's off, and in RM Smartsheet, their setting is set to reflect that.

When I push it through to RM smartsheet, the system doesn't allocate for example 1.25 hours between the 8 days Mon - Thursday. Instead it allocated 8 hours, 1 hour per day Mon-Thurs, have you noticed this? and how have you got around it? As the scheduled hours will be wrong with 8 and not 10.

Thanks!

Jez

Mark Ryan

Hi @Jez Akali,

That's one of the gotcha's with this, as you can't specify non-working days for individual resources. It would be great if tasks duration automatically adjusted if they are scheduled on a non-working day (not just for resources that only work a 4 day week, but also things like PTO and National Holidays), but alas not.

We are just strict with the planning, so that we split the tasks if they span a "non-standard" non-working day.

Thanks,

Mark

Jez Akali

Ah right, that's not ideal for us as each phase can be for every couple of months. We will stick to using RM Smartsheet for this one, I suppose if I really wanted to I could get a formula that works out the duration minus the Friday's between those dates.

Thank you for your help @Mark Ryan, much appreciated!