I'm trying to write a tracking form for the clinicians in my office that will help them see which clients are due for a treatment review. When a client starts therapy, their chart must be reviewd every six months. The clinicians will enter their clients start dates, and the worksheet will automatically calculate the 6,12,and 18month due dates using the EDATE formula. I would like the color of a particular cell to change based on how close the due date is to today. For example, if the due date is within 30 days, turn the cell green. If the due date is within 10 days, turn the cell yellow. If the due date is past-due, turn it red. I have used conditional formatting to accomplish this, but the problem lies in turning CF off after the chart review is completed, then changing the cell colors based on the next due date.
So, if today is 3/5/12, and the due date is 4/4/12, the cell (B2) should be green. When the date is 3/26/12, B2 should be yellow. If the date is 4/5/12, B2 should turn red. Once I enter a completion date in another cell, B2 should revert to white. Now that the 6-month due date has been satisfied, I want to remind the clinician about the 12-month due date, and start the process over with the new due date, and not reactivate the original 6-month deadline.
I've tried to use conditional formatting, but once the due date has passed, the cell will stay red. If a clinician enters all their clients, and some of them have already had their 6 or 12 month reviews, the CF turns them all red, even though the review has been done; I don't want to flag them as delinquent.
Should I just set up conditional formatting on each of the "Due Date" cells to turn them a color until a date is entered in the "Completion Date" column? Will conditional formatting work if the EDATE function
is being used in the cell and not user data?
Is it possible to use a combination of IF, ISTEXT, and EDATE in a conditional formatting statement?
E.G: =IF(there is text in this cell,nothing happens,conditional formatting applies).
Thank you in advance for any help.
So, if today is 3/5/12, and the due date is 4/4/12, the cell (B2) should be green. When the date is 3/26/12, B2 should be yellow. If the date is 4/5/12, B2 should turn red. Once I enter a completion date in another cell, B2 should revert to white. Now that the 6-month due date has been satisfied, I want to remind the clinician about the 12-month due date, and start the process over with the new due date, and not reactivate the original 6-month deadline.
I've tried to use conditional formatting, but once the due date has passed, the cell will stay red. If a clinician enters all their clients, and some of them have already had their 6 or 12 month reviews, the CF turns them all red, even though the review has been done; I don't want to flag them as delinquent.
Should I just set up conditional formatting on each of the "Due Date" cells to turn them a color until a date is entered in the "Completion Date" column? Will conditional formatting work if the EDATE function
is being used in the cell and not user data?
Is it possible to use a combination of IF, ISTEXT, and EDATE in a conditional formatting statement?
E.G: =IF(there is text in this cell,nothing happens,conditional formatting applies).
Thank you in advance for any help.
