Project Status Color in Status Field

Michele Rosenberg

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

John_Foster

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

John_Foster

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

johngraham78

@Michele R.

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"

))

Michele Rosenberg

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"))

John_Foster

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

Michele Rosenberg

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)

John_Foster

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

Michele Rosenberg

I guess that would help @John_Foster :) Thank you! Friday brain. Have a great day!