Project Status Color in Status Field
Hello community!
I want to put a field in summary to query the project end date in the project end date field within sheet summary and apply a RYG status based on a defined amount of time to end date. Is this possible? Does this even make sense? Is there a better solution someone has achieved?
Project End Date = 11/30/2023 - Still In progress
Best Answers
-
Hi @Michele R. ,
I believe that there was an extra [end date] in the formula you were using. In addition, I have modified the output values to the way they display with capitalisation.
=IF([End Date]1-TODAY()>30,"Green",IF(AND([End Date]1-today()<30,[End Date]1-today()>0),"Yellow","Red"))
Hope this helps!
John
-
Hi @Michele R. ,
I have looked at the formula you have used and you do not have two arguments. I have modified the formula as per below which should work.
=COUNTIF([End Date]:[End Date], @cell > TODAY())
Thanks,
John
Answers
-
You could use IF statements and greater than or less than signs. Something like this
IF([end date]-today()>30,"green",
IF( AND( [end date]-today()<30,[end date]-today()>0 ),"yellow","red"
))
-
Thank you for the idea John, unfortunately that isn't working for me in sheet summary. I know it is just syntax error but not sure where.
=IF([End Date]1-TODAY()>30,"green", IF(AND([End Date]1-today()<30,[end date][End Date]1-today()>0),"yellow","red"))
-
Hi @Michele R. ,
I believe that there was an extra [end date] in the formula you were using. In addition, I have modified the output values to the way they display with capitalisation.
=IF([End Date]1-TODAY()>30,"Green",IF(AND([End Date]1-today()<30,[End Date]1-today()>0),"Yellow","Red"))
Hope this helps!
John
-
THANK YOU! This worked perfectly. Now what if I wanted to count all of the tasks greater than zero? I thought I could do this but get invalid argument set.
=COUNTIF([End Date]:[End Date] - TODAY() > 0)
-
Hi @Michele R. ,
I have looked at the formula you have used and you do not have two arguments. I have modified the formula as per below which should work.
=COUNTIF([End Date]:[End Date], @cell > TODAY())
Thanks,
John
-
I guess that would help @John_Foster :) Thank you! Friday brain. Have a great day!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!