Formulas will be the death of me-

Bridget Clawson Braaten

Firstly, formulas will be the death of me. My coworker wants to to do this-

If the overall plan is 25% complete and 25% through the duration = green

If the overall plan is 50% complete and 50% through the duration = green

If the overall plan is 75% complete and 75% through the duration = green

If the overall plan is 100% complete and 100% through the duration = green

If under the above bench mark but not 0% = yellow

If not at the above bench mark and 0% complete= red

I started this- but I think I am failing. The random 56 is 25% of the duration. I am not sure I like this guy enough to help any more (kidding)- thoughts?

=IF(AND(% Complete@row >24, [Due Date]@row - TODAY() > 56), "Green", IF(AND(% Complete@row >50, [Due Date]@row - TODAY() > 112), "Green", IF(AND(% Complete@row >75, [Due Date]@row - TODAY() > 168), "Green", IF(AND(% Complete@row <24, [Due Date]@row - TODAY() > 56), "Red", IF(AND(% Complete@row <50, [Due Date]@row - TODAY() > 112), "Red", IF(AND(% Complete@row <75, [Due Date]@row - TODAY() > 168), "Red",)))))))

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

Mike Wilday

Well. Let's see if I can help haha.

First off, I would make the percentage of days dynamic by creating a percentage column in the primary sheet that determines how far along the current task should be based on duration. It might be nice to see in a new column I am calling TimePercentage. Or I included a formula that does the math for you and embeds it into the color of the project.

For Calendar Days

=NETDAYS(TODAY(), [End date]@row) / NETDAYS([Start date]@row, [End date]@row)

This will calculate the current duration percentage of the project based on Calendar Days

For Working Days

=NETWORKDAYS(TODAY(), [End date]@row) / NETWORKDAYS([Start date]@row, [End date]@row)

Then your comparison formula can compare if the current project percentage matches the percentage of the duration.

=IF([% Complete]@row >= [TimePercentage]@row, "Green", IF([% Complete]@row > 0, "Yellow", "Red"))

OR you can embed the Time percentage in one single RGB symbol column like this...

For Calendar Days

=IF([% Complete]@row >= (NETDAYS(TODAY(), [End date]@row) / NETDAYS([Start date]@row, [End date]@row)) , "Green", IF([% Complete]@row > 0, "Yellow", "Red"))

For Working Days

=IF([% Complete]@row >= (NETWORKDAYS(TODAY(), [End date]@row) / NETWORKDAYS([Start date]@row, [End date]@row)), "Green", IF([% Complete]@row > 0, "Yellow", "Red"))

Here is a screenshot showing the working formulas...

All comments

Bridget Clawson Braaten

@maria.watters@state.co.us

Mike Wilday

Hi @Bridget Clawson Braaten

Formulas are fun for some. But this is a beast. What are the challenges this formula is presenting you? What questions do you have about it? Is it failing somewhere specific? Or are you just venting -- which is TOTALLY understandable. haha.

Bridget Clawson Braaten

Thank you for saying this! Generally I use this go to formula - =IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() < 10), "Red", IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() > 30), "Green", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() < 10), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", IF(ISBLANK([Start Date]@row), "", ""))))))))

But this coworker wants something different where based on the duration of the entire project, the health changes at certain percentages and stages of time left to complete. My formula only got as far as the green health piece. He would like red health if outside certain parameters. So part of me is venting and part of me would like some superhero that has made formulas based on percentages + durations to give me a few tips. I am used to status/end date combos for health (see above).

Mike Wilday

Well. Let's see if I can help haha.

For Calendar Days

=NETDAYS(TODAY(), [End date]@row) / NETDAYS([Start date]@row, [End date]@row)

This will calculate the current duration percentage of the project based on Calendar Days

For Working Days

=NETWORKDAYS(TODAY(), [End date]@row) / NETWORKDAYS([Start date]@row, [End date]@row)

Then your comparison formula can compare if the current project percentage matches the percentage of the duration.

=IF([% Complete]@row >= [TimePercentage]@row, "Green", IF([% Complete]@row > 0, "Yellow", "Red"))

OR you can embed the Time percentage in one single RGB symbol column like this...

For Calendar Days

=IF([% Complete]@row >= (NETDAYS(TODAY(), [End date]@row) / NETDAYS([Start date]@row, [End date]@row)) , "Green", IF([% Complete]@row > 0, "Yellow", "Red"))

For Working Days

=IF([% Complete]@row >= (NETWORKDAYS(TODAY(), [End date]@row) / NETWORKDAYS([Start date]@row, [End date]@row)), "Green", IF([% Complete]@row > 0, "Yellow", "Red"))

Here is a screenshot showing the working formulas...

Bridget Clawson Braaten

You are my freaking hero-

Mike Wilday

Hahaha. Glad I could be of help. That was a fun puzzle to solve. :)

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.