TotalFloat won't actually calculate Total Slack or Free Slack.

breso
breso ✭✭✭
edited 09/13/22 in Formulas and Functions

Problem Statement:

  • We need to calculate Total Slack. This is defined in this Smartsheet Guidance (Slack Time in Project Management | Smartsheet) as, “Total slack is the amount of time [days] a task can be delayed without impacting the delivery of the project.” So, for any tasks on the critical path, Total Slack should be nearly 0 days. Whereas all tasks not on the critical path should have plenty of days that they can be delayed before they impact the project end date.
  • The TOTALFLOAT() formula keeps calculating 0 days for all tasks regardless of impact to the project schedule. I tried to calculate the Free Slack on my own below since the TOTALFLOAT formula was not working, but it is far from what we need.
  • I included a quick example of what I am trying to emulate from MS Projects. I compared the results between MS Project and Smartsheet in the attached screenshots.

Formula (Column: Total Slack):

=IF([% Complete]@row = 1, 0, IF(COUNT(CHILDREN()) > 0, MIN(CHILDREN()), TOTALFLOAT([Task Name]@row)))

Formula (Column: Free Slack):

=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))))

Formula (Column: Critical):

=IF(COUNT(CHILDREN()) > 0, IF(SUM(CHILDREN()) > 0, 1, 0), IF(ISCRITICAL([Task Name]@row) = true, 1, 0))

Formula (Column: Row Number):

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0) - - where ROW ID is a # Auto Number column



Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @breso

    I've tested your TOTALFLOAT formula and it works as expected on my sheet: tasks that are not on the critical path show a number, versus 0.

    At this point I believe the cause may have to do with the complexity of how your sheet is set up (levels of hierarchy, multiple predecessors) and it would be best to troubleshoot this directly with Support so you can share full screen captures and detailed information in a private channel.

    Totalfloat is relatively new, so it's good to see how it's being used and where any inconsistencies are showing up! Thanks for posting.

    Cheers,

    Genevieve

  • acreerl
    acreerl ✭✭

    Hi @breso,

    Were you able to find a solution for Total Slack in Smartsheet? I've incorporated a calculation like yours with Free Slack, but Total Slack would be quite helpful for my situation and I can't seem to figure out a way to manually calculate it since TOTALFLOAT() is not working as I would expect it to.

    Cheers,

    @acreerl

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!