Calculate buffer for a task/chain before it affects the project completion date?

Trying to calculate the buffer a task or chain of tasks has before it affects the project completion date.

iscritical allows me to determine if on critical path, in which case its 0 days buffer.

I'm trying to leverage successors function to see if i can determine the buffer, but no matter what i try it just gives me the row number of the successors or mic/max successor.

from there i used an index/match to find the min successor current start, and then subtract that from the task's current end.

this works to find the final task in a succesors chain to determine the buffer, but all predecessor tasks in that chain are showing 0. in addition, the buffer does not take into account any holidays or things connected to resource management addon.

is there a way to do this? or is it not possible?

MIN SUCCESSOR START

=IF(COUNT(SUCCESSORS([Task Name]@row)) = 0, [Current Start]@row, IF(COUNT(SUCCESSORS([Task Name]@row)) > 0, INDEX([Current Start]:[Current Start], MIN(SUCCESSORS([Task Name]@row))), ""))

BUFFER?

=IF(ISCRITICAL([Task Name]@row), 0, IF([Min Successor Start Date]@row = "", "?", NETWORKDAYS([Current End]@row, [Min Successor Start Date]@row)))

Find more posts tagged with

Formulas

Project Management

Accepted answers

Matt Stewart

i ended up ditching the SS project settings and resource management entirely and building my own project plan manually linking dependencies with a backtrack plan built off predecessors and a forward track plan built off successors, then got the business days between the current task end date and the planned end date to give the buffers.

All comments

Genevieve P.

Hi @Matt Stewart

The SUCCESSORS function currently only returns the row number of a successor, as you've found, so the way you have this set up right now sounds like the best way to identify the buffer currently.

NETWORKDAYS should exclude weekends, but if you have additional holidays identified in your Project Settings, you'll want to create a helper date column in your sheet to list out the other dates to exclude, then add that at the end of the function.

NETWORKDAYS( start_date, end_date, holidays)

See: https://help.smartsheet.com/function/networkdays

Please also provide your feedback to the Product team through this form, here!

Thanks,

Genevieve

Matt Stewart

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.