Conditional Formatting Help

Hi,

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)
[ATTACH=CONFIG]66405[/ATTACH]

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.

Thankyou!

Re: Conditional Formatting Help

Does anyone even have a workaround as opposed to a solution?

Re: Conditional Formatting Help

forum.ozgrid.com/index.php?attachment/66437/

Have i uploaded it correctly?

Re: Conditional Formatting Help

Thankyou for taking the time to reply.

Would that solve the problem of the target date going red despite the target time being met?

So if target date was 28/7/15, and the actual sales date was the 27/7/15 - I would want target date to remain green. With the now formula i am using just now - it will always go red regardless(as i will have months worth of historical data on this file). Hope im making sense! :-/

Re: Conditional Formatting Help

Hello,

Apologies for not being clear. (and thanks for taking the time to help me).

Yes, it is column M I am trying to format.

WHen i enter a date in column K that will automatically give a deadline in column M (28 Days). Column M should remain green until 7 days before, when it should go orange. And red when it goes over the deadline.

I am fine with all of the above - the complciation is when the sale is complete before the deadline. If the date in column N is before the deadline then M should remain green. Because I am using the NOW formula every date I enter in M will go red regardless of whether it has met the deadline date or not. As you can prob see from my current formatting I have tried a few different formulas to solve this but to no avail.

Please ask if any more questions or I havent explained my problem clearly.

Regards

Re: Conditional Formatting Help

That is asbolutely spot on. Thank you very much!

Greatly appreciated!

Re: Conditional Formatting Help

I am so sorry to ask for your help again... the spreadhseet / formula you done is fantastic and I have been able to replicate it to many different files i use, however ther is one small anomoly. When the actual date is the same as the deadline date - the box goes red, when i would like it to be green.

At the moment the green formula is =OR(S3-TODAY()>7,AND(T3<>"",T3<S3)), I have tried adding in =OR(S3-TODAY()>7,AND(T3<>"",T3<S3,T3<>S3)) but this hasnt worked.

DO you have any ideas!?

Thank you!

Re: Conditional Formatting Help

I am so sorry to ask for your help again... the spreadhseet / formula you done is fantastic and I have been able to replicate it to many different files i use, however ther is one small anomoly. When the actual date is the same as the deadline date - the box goes red, when i would like it to be green.

At the moment the green formula is =OR(S3-TODAY()>7,AND(T3<>"",T3<S3)), I have tried adding in =OR(S3-TODAY()>7,AND(T3<>"",T3<S3,T3<>S3)) but this hasnt worked.

DO you have any ideas!?

Thank you!

Re: Conditional Formatting Help

ahh, the = sign!

I am so slow sometimes! Thank you so much!