Formulas will be the death of me-
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",)))))))
Best Answer
-
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...
Answers
-
@maria.watters@state.co.us
-
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.
-
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).
-
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...
-
You are my freaking hero-
-
Hahaha. Glad I could be of help. That was a fun puzzle to solve. :)
Help Article Resources
Categories
Check out the Formula Handbook template!
