Possibly an =IF statement??

[Sawdeeka01]

Hi, I am hoping someone could assist me please.

I am helping someone with a spreadsheet and they are wanting to flag dates at various stages.

Essentially they are looking for two queries;

- When 10 business days have passed column P (Closing date), column W (Selection Report finalised) will highlight orange (please note at this stage column W will be blank), and;
- When 19 business days have passed column P (Closing date), column W (Selection Report finalised) will highlight red (please note at this stage column W will be blank).

Any help you could provide would be awesome, thank you so much and I hope you have a great day!

 

[Sawdeeka01]

It works perfectly in a fresh spreadsheet but still not working in the master, grrrr!!!

 

[Peter_SSs]

It works perfectly in a fresh spreadsheet but still not working in the master, grrrr!!!

Do you have any other existing Conditional Formatting in column W?
If not, select all of column W and Home - CF - Clear Rules - Clear Rules from Selected Cells
Now Select from W2 down to the end of your data but with W2 is showing as the active cell
Home ribbon tab -> CF -> New rule ... -> Use a formula .. -> Format values where ...:- =AND(W2="",TODAY()>WORKDAY.INTL(P2,10)) -> Format... -> Fill tab -> Choose orange -> OK -> OK

Any joy?

 

[Sawdeeka01]

Hi,

I am so sorry about the delay, I have been off work due to illness.

I have rebuilt the entire spreadsheet this morning and followed the steps to conditionally format the cells in question. It is working but it will not hold both queries at once. If I set the CF to 10 days (orange) it works perfectly, If I add the second CF for 19 days (red) it completely overrides the first CF so I am only seeing the one colour highlight cell W. It is the same if I reverse it and do the 19 days first, once I add the second query it overrides the first.

Sorry I hope I am not being a pain!

 

[Peter_SSs]

Sorry I hope I am not being a pain!

No. If it isn't working we need to try to work out why.

If you select the CF range in column W and go to Conditional formatting - Manage rules ... do you see the rules in this order and with appropriate ranges shown in the 'Appliws to' boxes?

 

[Sawdeeka01]

No. If it isn't working we need to try to work out why.

If you select the CF range in column W and go to Conditional formatting - Manage rules ... do you see the rules in this order and with appropriate ranges shown in the 'Appliws to' boxes?

View attachment 41240

Good morning,

I started again this morning and BINGO, it is finally working! Thank you so so much for your patience and assistance, I have really appreciated all input!

Have a great day today whereever you may be,

Sawdeeka01
(Oz)

 

[Peter_SSs]

BINGO, it is finally working!

Good news! Thanks for letting us know.