Forum Discussion
How to automatically change the cell color if the date is modified by a separate list of dates.
Hi,
I have a list of dates that specific tasks must be completed by.
If these specific task dates falls on a day that the company is closed, I have an equation that will automatically change the date to the previous work day.
How can I automatically have the cell color change to let me know that the original date in the cell has been modified by the equation/closed date?
Thanks,
Scott Williams
7 Replies
- Are the projects completion date based on the projects commencing date? What criteria is used to determine the completion date (7 days after the start date) ? You could add an conditional formatting, IF function that says something like IF(D4=,Your criteria+7) [green] and a second that says IF(D4<, Your criteria+7) [red] Make sure to check the box to stop if true next to the first condition.
DRCRAWL Thanks for the reply. The due dates are determined by an equation. The due date is the date that the product is required to be in stores (column J, "In Store Date"). There are several prior steps that require due dates so all parties are aware of when their particular task is due. These dates are all based off the final instore date and working backwards to determine all task dates. Each cell working backwards has an equation to determine that specific date which also refers to a list of closed holiday dates. The list of "Closed For Holiday" dates are listed in column V (V6:V16). The equation calculates if the original cell date falls on one of the "Closed For Holiday" dates. If the original cell dates falls on a "Closed For Holiday" date, the cell will automatically move backwards on the calendar to the previous workday.
I have personally and painstakingly identified and highlighted the cells in blue that were modified by the "Closed For Holiday" date list for easy identification of the dates that were automatically modified.
This equation is from cell i7.
=WORKDAY(J7,-5,V6:V16)
I have attached a pic below and also uploaded a copy of the spreadsheet for reference.
Is there an equation or conditional formatting that would assist in automatically highlighting these modified cell dates?
Scott_Williams_51266 In K7 (and copy down), perhaps this:
=IF(WORKDAY(J7,-5)<>I7,"Date Altered for Holiday","")It compares the actual In-store date (I7) with the date if there had not been any holidays. If it's different, then return the text, otherwise nothing.
As a comment, IF() is not necessary. =condition is enough.
Also in that case it's not necessary to check Stop Box. Conditional formatting stops evaluating rules for the cell on first rule which returns TRUE.
