Task Health: formula dependents on Creation Date

Hello there,

I need to automate the Task Health column basing on the following criteria:


Blue: creation date is in the last 3 months

Green: creation date is between the last 3 and 6 months

Yellow: creation date is between the last 6 and 9 months

Red: creation date is more than 9 months old


Thanks for your help

Tags:

Best Answer

  • John_Foster
    John_Foster ✭✭✭✭✭✭
    Answer ✓

    Hi @a.antuzzi,

    Try this formula, it needs to be copied into the health column, once added to the first row right click and select the "Convert to Column Formula" to apply it to all rows.

    =IF(DATEONLY(Created@row) > TODAY() - 90, "Blue", IF(DATEONLY(Created@row) > TODAY() - 180, "Green", IF(DATEONLY(Created@row) > TODAY() - 270, "Yellow", "Red")))

    The formula is crude in terms of the using the number of days rather than precise months.

    John

Answers

  • John_Foster
    John_Foster ✭✭✭✭✭✭
    Answer ✓

    Hi @a.antuzzi,

    Try this formula, it needs to be copied into the health column, once added to the first row right click and select the "Convert to Column Formula" to apply it to all rows.

    =IF(DATEONLY(Created@row) > TODAY() - 90, "Blue", IF(DATEONLY(Created@row) > TODAY() - 180, "Green", IF(DATEONLY(Created@row) > TODAY() - 270, "Yellow", "Red")))

    The formula is crude in terms of the using the number of days rather than precise months.

    John

  • My column name is "Creation Date" so I modified your formula like this:

    =IF(DATEONLY(Creation Date@row) > TODAY() - 90, "Blue", IF(DATEONLY(Creation Date@row) > TODAY() - 180, "Green", IF(DATEONLY(Creation Date@row) > TODAY() - 270, "Yellow", "Red")))

    ... but I get #UNPARSEABLE...

    When I renamed the column to remove spaces, the formula worked properly.

    How can I edit the formula in order to keep spaces in the column name?

    Thanks

  • florian.zbinden7
    florian.zbinden7 ✭✭✭✭✭
    edited 11/29/23

    hi @a.antuzzi,

    when you have a space in the column name, you need to have the columns in [], like this : [Creation Date]

    an example :

    best regards,

    Florian

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!