TotalFloat with multiple phases

Options
breso
breso ✭✭✭✭
edited 02/17/23 in Formulas and Functions

Hello, are there recommended best practices for handling dependencies when calculating TotalFloat, =TOTALFLOAT([Primary Column]@row)?

In this example, Milestone 0 requires that Tasks 1-3 be completed and is a predecessor for beginning the next project phase. TotalFloat calculates values for tasks 5 & 6, but it does not calculate for any iteration of task 3 that rolls up into Milestone 0. To troubleshoot this issue, I added a Phase 1.5 that does not rollup into M0 and the children tasks actually give float values...

With every phase I add it breaks the float calculations for milestones in preceding phases. For example, adding Phase 3 with multiple dependencies on M2 breaks the float calculation for M1.5.

Any feedback is appreciated!


Best Answer

  • Julio S.
    Julio S. Moderator
    Answer ✓
    Options

    Hi @breso,

    It looks like the way Smartsheet and MS Projects calculate Slack time is different and that seems to be the root of the behaviour you experience here. I've found this article that contains key tips on how Smartsheet calculates Slack time, which confirms that any tasks with any interaction with the Critical Path (as your task 3) won't have the option to calculate Slack time. The way to have slack time calculated for Task 3, it would be to completely remove it from the Critical Path. You may test this by removing all predecessors to Task 3 in Milestone 2.as this should bring Task 3 out of the Critical Path.

    With this being said, if you'd like to request a Free Float function in Smartsheet, when you have a moment, please submit your feature request to the Product team by signing in to the online Community (the Community uses your Smartsheet account to sign in) and create an Idea post in the Smartsheet Product Feedback and Ideas topic

    Posting your enhancement in the Community will allow other Smartsheet users to see and vote on your idea! The top-voted posts in this category are reviewed monthly by the Product team and you'll receive an email notification if a status changes for a post you've created or voted on. 

    I hope that this can be of help.

    Cheers!

    Julio

Answers

  • Julio S.
    Julio S. Moderator
    edited 02/23/23
    Options

    Hi @breso ,

    It seems like tasks that are part of the critical path will return a result of 0 in the formula because they contribute to the calculation of the Project End Date, therefore, any deviation of this would affect the project end date.

    If you wish to calculate for how long a task can be delayed without impacting the Project Finish Date, such tasks will need to be completely outside the critical path. This means that they can't be connected in any way (can't have predecessors or successors) with any task that is part of the critical path. In your example, Milestone 2 has a predecessor (Task 7 [row 19]) which is part of the critical path and this seems to bring it outside the totalfloat calculations. For the same reason tasks under Phase 3 which are calculated based on Milestone 2 are now part of the critical path and therefore they continue to return a 0.

    I hope this makes sense.

    Cheers!

    Julio

  • breso
    breso ✭✭✭✭
    edited 02/23/23
    Options

    Hi @Julio S. ,

    I appreciate you taking the time to respond. Most of the projects I work on have complicated network diagrams where there are multiple parallel activities/phases feeding sequential critical activities, so all my float values return 0 or negative values in SmartSheet. So, I really need a method for determining how much buffer there is for each of those parallel phases/activities such as Task 3 above. Is there a free slack/float formula?

    I exported the sheet to MS Project. The total float (slack) was calculated for each iteration of Task 3 as shown below. Which would be the correct implementation of the formula? As the example below helps me know how much each task can slip without delaying the project.

    Is it worth submitting to the developers as an issue with the formula? or a feature request for free float?


  • Julio S.
    Julio S. Moderator
    Answer ✓
    Options

    Hi @breso,

    It looks like the way Smartsheet and MS Projects calculate Slack time is different and that seems to be the root of the behaviour you experience here. I've found this article that contains key tips on how Smartsheet calculates Slack time, which confirms that any tasks with any interaction with the Critical Path (as your task 3) won't have the option to calculate Slack time. The way to have slack time calculated for Task 3, it would be to completely remove it from the Critical Path. You may test this by removing all predecessors to Task 3 in Milestone 2.as this should bring Task 3 out of the Critical Path.

    With this being said, if you'd like to request a Free Float function in Smartsheet, when you have a moment, please submit your feature request to the Product team by signing in to the online Community (the Community uses your Smartsheet account to sign in) and create an Idea post in the Smartsheet Product Feedback and Ideas topic

    Posting your enhancement in the Community will allow other Smartsheet users to see and vote on your idea! The top-voted posts in this category are reviewed monthly by the Product team and you'll receive an email notification if a status changes for a post you've created or voted on. 

    I hope that this can be of help.

    Cheers!

    Julio

  • breso
    breso ✭✭✭✭
    Options

    Hi @Julio S.

    Thank you for the insight! I submitted the feature request like you recommended. Having the free float formula will at least give me a refence for schedule buffer. I tried to create one myself, but I couldn't get it to sort through all the successor start dates.

    =IF(COUNT(CHILDREN()) > 0, MIN(CHILDREN([Free Slack]@row)), IF(COUNT(JOIN(SUCCESSORS([Task Name]@row), ", ")) = 0, NETWORKDAYS([End Date]@row, MAX([End Date]:[End Date])), NETWORKDAYS([End Date]@row, INDEX(COLLECT([Start Date]:[Start Date], [Row Number]:[Row Number], MAX(SUCCESSORS([Task Name]@row), ", ")), 1))))

    Thanks!!

    Brandon

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!