Color change on format rule for one week

Hi All,

I need suggestion, How to set format rule for a period of time.

Ex:- I have a table which list the percentage of some progress. When the progress is 100%, I want to color GREEN for a week. I gave an expression which make the data GREEN color if the value is above 0.99 but how can I show color or format rule to be applicable for one week only ?

Please let me know.

Thanks,
Faraaz Shaik

If you have a Date type column monitoring the progress when reaching 100% with initial value of Today() which can be utilized as part of the “GREEN” expression, you might be able to do something like this:

	AND(
		WEEKNUM(TODAY())=WEEKNUM([Date]),
		your-GREEN-expression
	)

Which should only make the color green for a week time (the same week as when it changed to 100% captured by the [Date])

1 Like

Will it save the color for a week or 5 days ?

That will save the color when TODAY() week number is equal to the [Date] week number.

For keeping the color for 5 days after it passed, you may use below expression:

AND(
    ABS((Hour(Today()-[Date])/24))<=5,
    (Hour(Today()-[Date])/24)>=0,
    your-GREEN-expression
)

Please note that I make the less than 5 as absolute number just to make me easy to understand it.