Unable to add up duration for metrics calculation


I am trying to use cross-sheet formula to add up the planned duration for certain workers, for performance analysis purpose.

After setting up the Actual Start and End date, we copied it to the Planned duration, Plan start, Plan end - as a baseline reference.

When I am trying to calculate the Planned days by employee, it just come up as zero. May I know what could have gone wrong? Should I first convert the 5d to just numerical 5? How can I do that? the planned duration could be 1, 2 or 3 digits.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You can only have one "true" duration column where "5d" can be counted as the number 5. Any other column with "5d" in it will be treated as a text value and as such cannot be summed.

    My suggestion would be to use a helper column (can be hidden to keep the sheet clean) where you strip out the "d" and convert it to a numerical value that can then be summed.

    =VALUE(SUBSTITUTE([Planned Duration]@row, "d", ""))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!