Can't Set Column Formula for Duration - Work Around Available?

JAmirali

I have several sheets that are tracking the time a field employee is working at a specific location. This is determined by the Current Date minus Start Date to give me the number of days. I set the output column to be a Duration column so that it would automatically input the "d" at the end.

For my purposes, this needs to be a column formula so that it can autofill when new rows are data shuttled into the sheet from other reports that I receive. I noticed that Smartsheet does not allow this.

I set the column as Text instead and wrote the formula to add the "d" after the fact.

It seems that Smartsheet is reading the addition of "d" in a very strange way. I have status balls that are linked to the date column and are set to be a certain color depending on the number of days I specified. Green for the least amount, then yellow and red when the employee is close to 1 year.

When I added the "d", certain rows that were a Green ball suddenly became Red. Some remained the same (Yellow stayed Yellow). There did not appear to be a pattern, but it was clear that it was reading it as additional days for whatever reason. This occurred if I added "d", if I did a space between the letter and number, or even if I wanted it to include "days" as the full word.

Does anyone know why this is? I am ready to give up and just have the column be Text and not worry about the "d", but it just looks better and more clear to have the letter at the end.

Mark.poole

This is because adding the D changed the output from a numeric value to a text value. Try instead to create a helper column that has the Numeric value "This can be hidden" and the column that has the d is really just there for that. you would instead just use the helper column for the formula that's used for your symbols.

Mark.poole

This is because adding the D changed the output from a numeric value to a text value. Try instead to create a helper column that has the Numeric value "This can be hidden" and the column that has the d is really just there for that. you would instead just use the helper column for the formula that's used for your symbols.

JAmirali

Thank you for this explanation and for the helper column suggestion! I was hoping I would not have to do that for the sake of simplicity but I will definitely consider it.