The spreadsheet tracks maintenance renewals. We will either decide to renew the maintenance, or not. Assuming we desire to renew maintenance, the spreadsheet has conditional formating to indicate urgency: green, yellow or red. Green signifies time to start the renewal process.
I approached this a little differently. Currently the spreadsheet has hidden rows which contain today's date ($B$2), the end of this month date ($B$3), the end of next month date ($B$4), and the end of the second month in the future date ($B$5). The non-hidden B column cells have the current end date. Similarly, the C column has 1) the PO number for the renewal, 2) "N/A" if it is not going to be renewed, or is blank if more than two months out. The D column has the status of the renewal: Pending, In Work, Submitted, Complete, or Not Renewed. The conditional formatting specifies: 1) cell value is between $B$2 ans $B$3, 2) cell value is between $B$4 and $B$3, or 3)cell value is between $B$5 and $B$4.
Essentially it is red background with black text between today and the end of this month; yellow background with black text between the end of this month and the end of next month; or green back ground with white text between the end of next month and the end of the second month in the future. To convert to days, it would be red for 0-29 days; yellow for 30-59 days, and green for 60-90 days.
This works fine as a tickler for within 90 days. The problem arises when a desired renewal goes less than 0 days and is not completed. If I change the conditional formating for red to be less than 0 days, then all renewals go red whether they are completed or not. The goal is to expand the logic to highlight in red only those renewals that are 0 days (past due) and not in a "Complete" status. In the short term, this would highlight in process renewals that are not complete. In the long run, the red would highlight instances where we deliberately decided not to renew maintenance.
Thank you for your assistance with this.
Keep Up with TechRepublic