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.
Solved! Go to 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.
HOUR( TODAY() - [column] ) / 24 > 10
@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.
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.
User | Count |
---|---|
43 | |
25 | |
23 | |
14 | |
13 |