Conditional Formatting & Date Period

Admin@PAT

Hi Team,

I am wanting to apply CF to a sheet with a date field.

 

I would like to apply CF when a date falls (from todays date) within 3 months, another CF for 3-6months and another with 6+ months - thing is this becomes a 'rolling' CF, aeaning that at some point the CF will change or adapt based on todays date. I am not sure if this makes any sense...

 

Context: Leads List with column titled: Date Expected

Based on this date (Date Expected) the CF will apply.

 

So...

 

If 'Date Expected' is within 90days (from todays date) CF 'yellow' apply to row

 

If Date Expected is greater than 90days but less than 180 days (from todays date) CF 'Orange' and so on for great than 180days

 

Is this at all possible, I am happy with a workaround, also happy with a workaround to a formula that would return a value

 

If Date Expected is today + 60 return HOT, if +180 return MEDIUM etc... 

 

Sorry this is jumbled, I know what I need to achieve, just not how to get there as the CF options for Date columns seem limited in this instance.

Darrin Eisenbarth

Your first condition can be achieved via Conditional Formatting by using "is in the next (days)", because that condition uses days vs. a fixed date. However, the greater than 90 but less than 180 AND the greater than 180 days arguments are not achievable via CF conditions. Since the arguments needed to get those results force you to select an actual date.

What you can do is create a column for storing a value 1=within 90 days, 2=>90 and <=180, 3=>180. Use the IF formula against "date expected" to get the value.

Now you can set your CF based on the value of the column being 1, 2 or 3 and you can hide the column that holds the numeric value.

 

Hope that helps.

Admin@PAT

Hi Darren

Yes that is exactly it. Thanks, I just am not sure about the logic of the formula to return the 1, 2, or 3 result. 

 

Thanks - I will keep trying

 

Admin@PAT

Hi Everyone,

 

Can anyone please advise the logi formula to return a 1, 2 or 3 value.

 

if <date expected> (is less than 90days from today, return value '1'.

 

I am stumped!!!

J. Craig Williams

I hope you solved this already, but in case you did not ...

 

=IF([Date Expected]23 - TODAY() > 179, 3, IF([Date Expected]23 - TODAY() > 89, 2, IF([Date Expected]23 - TODAY() >= 0, 1, 0)))

 

will give the results

0 - in the past

1 - today through 89 days (or blank)

2 - 90 - 179 days

3 - 180 days and beyond.

 

Hope this didn't help (September seems a long time ago)

 

Craig