Dates and conditional formatting

[Tazzy666uk]

Hi, I am getting in a bit of spin trying to sort this myself, and would like some help please.

I have 3 columns I am referring to. Column G has a simple =F3+14 to give me 14 days on from the date in column F

However, what I would like (and my issue, which is stringing multiple formulas/conditional formatting together!!):


Assuming reference below to Row 3:

Text in G3 to be Green on days 1-4 of date in F3
Text in G3 to be orange on days 5-9 of date in F3
Text in G3 to be red on days 10-14 of date in F3
G3 cell to be filled red with white text when >14 days in F3

AND (sorry)

to not be conditional formatted at all once there is a date in Column H

If I cannot simply drag the formatting down the sheet, then this needs to apply to Rows 3-95

Hope that makes sense

Thank you so much in advance

Tazzy

 

[kevin9999]

Is this it?

Book1
	F	G	H
1	Date Received	Review Date by	Review Submitted
2
3	31/10/2023	14/11/2023
4	1/11/2023	15/11/2023
5	2/11/2023	16/11/2023	14/11/2023
6	3/11/2023	17/11/2023
7	4/11/2023	18/11/2023
8	5/11/2023	19/11/2023	14/11/2023
9	6/11/2023	20/11/2023
10	7/11/2023	21/11/2023
11	8/11/2023	22/11/2023	14/11/2023
12	9/11/2023	23/11/2023
13	10/11/2023	24/11/2023
14	11/11/2023	25/11/2023
15	12/11/2023	26/11/2023	14/11/2023
16	13/11/2023	27/11/2023
17	14/11/2023	28/11/2023
18	15/11/2023	29/11/2023
19	16/11/2023	30/11/2023
20
Sheet1

Cell Formulas
Range	Formula
G3:G19	G3	=F3+14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:G19	Expression	=AND(H3="",F3>=TODAY()-4,F3<TODAY()+1)	text	YES
G3:G19	Expression	=AND(H3="",F3>=TODAY()-9,F3<TODAY()+1)	text	YES
G3:G19	Expression	=AND(H3="",F3>=TODAY()-14,F3<TODAY()+1)	text	YES
G3:G19	Expression	=AND(H3="",F3<TODAY()-14)	text	YES

 

[Micron]

You can have more than 3 rules now (not counting the default when none of the rules apply)?

 

[Tazzy666uk]

Is this it?

Book1
	F	G	H
1	Date Received	Review Date by	Review Submitted
2
3	31/10/2023	14/11/2023
4	1/11/2023	15/11/2023
5	2/11/2023	16/11/2023	14/11/2023
6	3/11/2023	17/11/2023
7	4/11/2023	18/11/2023
8	5/11/2023	19/11/2023	14/11/2023
9	6/11/2023	20/11/2023
10	7/11/2023	21/11/2023
11	8/11/2023	22/11/2023	14/11/2023
12	9/11/2023	23/11/2023
13	10/11/2023	24/11/2023
14	11/11/2023	25/11/2023
15	12/11/2023	26/11/2023	14/11/2023
16	13/11/2023	27/11/2023
17	14/11/2023	28/11/2023
18	15/11/2023	29/11/2023
19	16/11/2023	30/11/2023
20
Sheet1

Cell Formulas
Range	Formula
G3:G19	G3	=F3+14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:G19	Expression	=AND(H3="",F3>=TODAY()-4,F3<TODAY()+1)	text	YES
G3:G19	Expression	=AND(H3="",F3>=TODAY()-9,F3<TODAY()+1)	text	YES
G3:G19	Expression	=AND(H3="",F3>=TODAY()-14,F3<TODAY()+1)	text	YES
G3:G19	Expression	=AND(H3="",F3<TODAY()-14)	text	YES

Hi Kevin,

Firstly, Thank you for taking the time to do this for me, it is appreciated.

The formulas work, which is great, providing there is a date entered in Column F cells.... If it is blank, the cell is red with an odd date in..... I have tried entering the cell formula without the conditional formatting and I get the same date come up. Once I enter a date in cell F it all works as it should.

Ideally, I would like cells in column G to be clear and if there is no date in F...

 

[kevin9999]

Could I suggest you make a slight change to your formula in column G, and a change to the last condition in the CF:

Book1
	F	G	H
1	Date Received	Review Date by	Review Submitted
2
3	31/10/2023	14/11/2023
4	1/11/2023	15/11/2023
5	2/11/2023	16/11/2023	14/11/2023
6
7	4/11/2023	18/11/2023
8	5/11/2023	19/11/2023	14/11/2023
9	6/11/2023	20/11/2023
10
11	8/11/2023	22/11/2023	14/11/2023
12	9/11/2023	23/11/2023
13	10/11/2023	24/11/2023
14	11/11/2023	25/11/2023
15	12/11/2023	26/11/2023	14/11/2023
16	13/11/2023	27/11/2023
17
18	15/11/2023	29/11/2023
19	16/11/2023	30/11/2023
20
Sheet1

Cell Formulas
Range	Formula
G3:G19	G3	=IF(ISNUMBER(F3),F3+14,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:G19	Expression	=AND(H3="",F3>=TODAY()-4,F3<TODAY()+1)	text	NO
G3:G19	Expression	=AND(H3="",F3>=TODAY()-9,F3<TODAY()+1)	text	NO
G3:G19	Expression	=AND(H3="",F3>=TODAY()-14,F3<TODAY()+1)	text	NO
G3:G19	Expression	=AND(H3="",ISNUMBER(F3),F3<TODAY()-14)	text	NO

 

[Tazzy666uk]

Could I suggest you make a slight change to your formula in column G, and a change to the last condition in the CF:

Book1
	F	G	H
1	Date Received	Review Date by	Review Submitted
2
3	31/10/2023	14/11/2023
4	1/11/2023	15/11/2023
5	2/11/2023	16/11/2023	14/11/2023
6
7	4/11/2023	18/11/2023
8	5/11/2023	19/11/2023	14/11/2023
9	6/11/2023	20/11/2023
10
11	8/11/2023	22/11/2023	14/11/2023
12	9/11/2023	23/11/2023
13	10/11/2023	24/11/2023
14	11/11/2023	25/11/2023
15	12/11/2023	26/11/2023	14/11/2023
16	13/11/2023	27/11/2023
17
18	15/11/2023	29/11/2023
19	16/11/2023	30/11/2023
20
Sheet1

Cell Formulas
Range	Formula
G3:G19	G3	=IF(ISNUMBER(F3),F3+14,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:G19	Expression	=AND(H3="",F3>=TODAY()-4,F3<TODAY()+1)	text	NO
G3:G19	Expression	=AND(H3="",F3>=TODAY()-9,F3<TODAY()+1)	text	NO
G3:G19	Expression	=AND(H3="",F3>=TODAY()-14,F3<TODAY()+1)	text	NO
G3:G19	Expression	=AND(H3="",ISNUMBER(F3),F3<TODAY()-14)	text	NO

Hi Kevin,

that is fantastic thank you. Added unintentional bonus that if I enter something in received with todays date or later, the cell font is black, not as per the conditional formatting, so I can populate the other cells in that row with what I ordered, and it now shows as black text to tell me it hasn't arrived yet..... perfect!

Really appreciate your help

 

[kevin9999]

Happy to help & thanks for the feedback