I've been working at this for a while and can't seem to figure out the formula.
I'm monitoring the status of projects as they move from stage to stage. There are 5 stages in every project which are always the same. What I would like to accomplish is show that a project has been stuck at a certain stage for longer 15 days by highlighting the cell that otherwise would be completed, had it been completed on time (this cell would be blank).
I am pulling this data from a program called Salesforce and updating my workbook daily, usually several times a day. So this data is changing as there are anywhere from 80-100 projects in progress at any given time, all of which are at various stages in the project.
I attached an example. The first two steps have dates signifying that the stages have been completed. It has been over 15 days since the most recent stage was completed. The stage that should have been completed by now 'Design Approved' - I want it to be shaded red to alert me of inactivity. I'm assuming that the TODAY function will need to be used considering the data is going to be updated throughout the day.