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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.