Column formula issue when using for resource allocation

Firstly, I love column formula. They are fantastic.

I do have an issue though. Our business has a lot of active projects and a lot of tasks which in timeframe may take weeks or days, however, only require hours of work from individuals.

To manage this I have created an Allocation column and an hours colum to allocate the correct % of work for individuals within that time period.

The formula looks like this.

=IF([Start Date]@row = "", "", Hours@row / (NETWORKDAYS([Start Date]@row, [End Date]@row) * 6))

This works well to provide an overview of the level of work for individuals over a period of time.

However, the formula keeps on disappearing and being replaced with values.

This happens across different sheets and only happens when the column is being used as resource allocation.

Any assistance with this would be greatly appreciated.

Thanks,

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Justin_Pennington

Formulas being supported in % Allocation columns when also used in Resource Management is a new release. It sounds like there may be something specific about your sheets and formula that is causing this unexpected behaviour. I would suggest reaching out to Smartsheet Support so they can work with you directly to find the cause of the issue!

I will note that formulas that return text and non numeric values are allowed, but will be ignored from a resourcing perspective. You may want to try wrapping a VALUE function around your output to ensure that it's numerical and not text... try this:

=IF([Start Date]@row = "", "", VALUE(Hours@row / (NETWORKDAYS([Start Date]@row, [End Date]@row) * 6)))

You will also want to make sure that in your project settings this column is set as the % Allocation column and not the % Complete column (since the % Complete column is currently unable to house formulas.)

Cheers!

Genevieve

Justin_Pennington

Thanks for the information @Genevieve P

I am not having any issue with non-numerical values, however will update my formula with your suggestion, thanks. And allocation in definitely linked to the allocation column.

I will contact support and see if they can help.

Thanks,

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.