Thank you in advance for anyone taking the time to help me! I am currently creating a monitoring tool for progress on sales.
I have an automated deadline column, where i would like it to be green up until 7 days before the date, amber for 0-7 days before the date, and reed for anything that has gone over this date. To do this I have used the formatting below (pic looks small, so for green e.g - cell value - less than - =NOW()=21)
The complicated part for me is when the sale is actually completed.
SO i want the basic rules i have stated above to apply, when the 'actual sale date' box is blank(i.e sale is still in progress). But if the 'actual sale date' box (i.e sale is complete) I would need the formatting in the 'target date' box to be influenced, as the =NOW formula i have been using will always go red eventually even if the sale has completed on time. I would like the target date box to stay green if it has been achieved within time, but red if achieved after target.
Hope this makes sense.
I have tried uisng ISBLANK fomula and AND forumla but to an avail. I am also aware theat the formatting rules need to be in a particular order for them to work correctly but I also cannot work this out. Any help would be massively appreciated!
I have uploaded a couple of screenshots but not sure how clear these are. An id also like to add I know my basic rules dont look like they are working in screenshot, but thats because i have been messing around so much with the other rules - so i can rectify these.