Format Rule should be activated a week before the start date

[faraaz]

Hi All,

Do we have any expression which can pickup the dates/days before the start date. I’m creating a format rule which highlight the color on data and I want to highlight 5 days before today() date ! Need Help in setting this up.

Thanks,
Faraaz Shaik

[hru]

You may use:

[Start Date]>=(TODAY()-5)

within the If-This-Condition-Is-True of the format rule, being [Start Date] is a date type, and select which columns to be highlighted.

[faraaz]

Thanks Heru, the expression you sent I have tied already and it works but I’m looking for some expression which only highlight the value or [start date] when the [start date] is approaching 5 days before today().

[faraaz]

I mean the [Start Date] should only be highlighted if the date is approaching 5 days before.

Let me know if you need more in detail.

Thanks,
Faraaz Shaik

[hru]

Hmmm have you selected ONLY the [Start Date] on the setting: “Format these columns and actions”

If that is not case, I might need a screen shoot of the format rule to ensure my understanding, since the above expression is just will do that.

Edit: maybe I am confused because we have 2 discussion about format rules, you may just correct my understanding on this.

[faraaz]

attaching a screen shot.

With the expression [Start Date]>=(TODAY()-5) it should only highlight the dates which are 5 days back instead it is highlighting all the dates which are lesser then today().

[hru]

ah… I see… Sorry for that. I have just tried something like below, perhaps you want to try:

AND(
    ([Mech_Strt_Dt]-5)>=TODAY(),
    WEEKNUM([Mech_Strt_Dt])<=(WEEKNUM(TODAY())+1)
)

there must be better answer than the above, but I can only think like that. Sorry for confusion.

[hru]

Hmmm I think I understand now, use this one instead of the above:

AND(
	Hour([Mech_Strt_Dt]-Today())/24<=5,
	Hour([Mech_Strt_Dt]-Today())/24>=0
)