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

Options

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.

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24 Answer ✓
    Options

    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24 Answer ✓
    Options

    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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • JAmirali
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!