Formula for Task Status using dropdown and Start | End dates

I'm trying to get a formula to work and I can't figure out what I've got wrong. Any help would be appreciated

I would like to update a Task status dropdown field (Not Started, In Progress, Completed, Past Due)

My formula is:

IF(Completed@row = 1, "Completed", IF(AND([Start Date]@row <= [TODAY Helper]@row, [End Date (Due Date)]@row > [TODAY Helper]@row), "In Progress", IF(AND([Start Date]@row < [TODAY Helper]@row, [End Date (Due Date)]@row < [TODAY Helper]@row, Completed@row = 0), "Past Due", "Not Started")))

It works if the Completed column is checked but otherwise I get an #INVALID OPERATION error message.

This is what I'm trying to do:

Completed is checked, "Completed"

Start Date less than or equal to Today Helper AND End Date (Due Date) is > Today Helper, "In Progress"

Start Date less than Today Helper AND End Date (Due Date) < Today Helper AND Completed unchecked, "Past Due"

Otherwise it should be "Not Started"

Screenshot 2023-02-23 at 10.59.15 AM.png

Thank for any help!

Peggy

Find more posts tagged with

Accepted answers

Colleen Patterson

@Peggy Parchert

Peggy, I am getting your formula to work exactly as written, in a text based column type, changing that column does not seem to change the results, but wanted to let you know. Have you confirmed that all date columns are set to Date functionality?

All comments

Colleen Patterson

@Peggy Parchert

Peggy Parchert

@Colleen Patterson - that was the problem. Thank you!!!

Colleen Patterson

@Peggy Parchert Its always the simple things that trip me up when writing formulas! Glad to be of help.

LaurieCass

I'm attempting a similar formula but using symbol chart instead of text. Any idea if it's possible to incorporate chart? For example: If In Progress and End Date >= TODAY, then "Green", etc.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.