Date rule format

[ten4info]

I want to color code text based on a date Bali and its relationship with Today() and +/- a number of days. One example, in XLS, this works:

Today - (the cell value) > 10

So if I take today less my date value, if it is greater than ten…then a cell is shaded.

How would I write it using Today() and its relationship to my date value (Last Contact Date) and whether it is + or - 10 (days).

I need to be able to do a variety of these type of format rules with adding and subtracting “x” number of days. Thank you.

[Marc_Dillon]

HOUR( TODAY() - [column] ) / 24 > 10

[ten4info · Accepted solution]

@Marc_Dillon, brilliant! I was able to take this formula and expound with IFS for additional qualifying components. Thanks so much.

My follow-up, if I first want to qualify (Yes/No) if “the contact” is present in another sheet, and then apply the above, how would I do that. Here is the premise, I’m building a CRM. The actual CRM component of the app, where the real automation plays out for Clients, includes ancillary folks that are necessarily prospects/clients. I have another sheet that lists prospects/clients and some additional information related to the engagement with these folks. Back to the CRM, I don’t care if I haven’t spoken to a competitor, vendor, or the like in 15 days, 30, 60, etc. BUT, I do want to track by color rules, how am I looking with regard to staying in front of prospects/clients.

Hopefully this makes sense. Thanks again.

[ten4info]

My follow-up, if I first want to qualify (Yes/No) if “the contact” is present in another sheet, and then apply the above, how would I do that. Here is the premise, I’m building a CRM. The actual CRM component of the app, where the real automation plays out for Clients, includes ancillary folks that are necessarily prospects/clients. I have another sheet that lists prospects/clients and some additional information related to the engagement with these folks. Back to the CRM, I don’t care if I haven’t spoken to a competitor, vendor, or the like in 15 days, 30, 60, etc. BUT, I do want to track by color rules, how am I looking with regard to staying in front of prospects/clients.