Task Health: formula dependents on Creation Date

a.antuzzi

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

John_Foster

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

John_Foster

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

a.antuzzi

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

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

a.antuzzi

Great! thank you