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]

Are you sure you want EXACTLY 10 and 19 Days ??
and NOT Greater than or equal to 10 and 19

Ohhhhh, it would be a greater than situation, not exactly, so very sorry!!

 

[Michael M]

Ok, so change the Today() to >=

=Today()>=$P$2.....

 

[Peter_SSs]

- 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).

It looks like this hasn't been taken account of so far either. Assuming business days are Mon-Fri, perhaps this?
I have also assumed from your description that once column W is filled in, any highlight should disappear.

21 06 09.xlsm
	P	Q	R	S	T	U	V	W
1	Closing date						Submitted	Finalised
2	23/05/2021
3	24/05/2021							4/06/2021
4	25/05/2021
5	26/05/2021
6	20/04/2021
7	20/04/2021							1/06/2021
8	29/05/2021
9	6/06/2021
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
W2:W9	Expression	=AND(W2="",TODAY()>WORKDAY.INTL(P2,19))	text	NO
W2:W9	Expression	=AND(W2="",TODAY()>WORKDAY.INTL(P2,10))	text	NO

 

[Sawdeeka01]

Ok that is working for the 19 days (red) but not the 10 days, do I need to do the 10 days query as between 10 and 18 days?

Ps Very grateful for your help!

 

[Peter_SSs]

Ok that is working for the 19 days (red) but not the 10 days,

Which post/suggestion are you referring to?

 

[Sawdeeka01]

Hi Peter,

Thank you for helping out. I thought the red was working for your query but neither are working, from either posts.

I do appreciate your help,

 

[Peter_SSs]

Does my sheet show the results that you would expect given the entries that I ahve made?

Can you post a small section of your worksheet like this with XL2BB showing the Conditional Formatting rules like I have?

 

[Sawdeeka01]

Yes your sheet is perfect and exactly what I am looking for. I am on a work computer and cant add any add-ins unfortunately. I have copied your formula but it still isn't working....

I am not sure how I can show you the formula without XL2BB?

Appreciate your time.

 

[Michael M]

Try using ALT + Print Screen....crop it down to a reasonable size ...then paste it back here !!
OR
Upload the file to a hsoting site, DropBox for instance, then paste the link to it back here.

 

[Peter_SSs]

All I can think of is that you are not setting up the CF correctly. Try these steps carefully

1. Start a completely fresh workbook
2. Manually enter the dozen or so dates exactly as and where I have them.
3. Select from W2 to W9 so that you have 8 cells selected but W2 is showing as the active cell
4. Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =AND(W2="",TODAY()>WORKDAY.INTL(P2,10)) -> Format... -> Fill tab -> Choose orange -> OK -> OK

Do any cells go orange?