Unable to add up duration for metrics calculation

Vivien Chong

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.

Paul Newcome

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", ""))

Paul Newcome

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", ""))

Vivien Chong

Thanks @Paul Newcome . I was trying to figure out the "VALUE" formula on my own, but couldn't seem to get the right results. Your formula works. Appreciate your help!

Paul Newcome

Happy to help. 👍️