Conditional Formatting

[JamieP89]

Morning All,

I wonder if I can ask for some guidance on whether there is a way to track say the last 7 days using TODAY within conditional formatting?

I'm looking for something like =A:A=MAX(TODAY()-7)

Needless to say the above isn't working for me. But Not sure what I'm doing wrong? (Assume A:A is the range of data I am looking for the max with) and not any date ranges. Wondering If because I haven't selected any dates to track that's the issues?

Hope this makes sense?

 

[Peter_SSs]

If this is not what you mean, please supply some sample data with XL2BB and explain in relation to your sample data exactly what should be highlighted and why.

22 09 20.xlsm
	A
1	25/09/2022
2	19/09/2022
3	25/09/2022
4	27/09/2022
5	1/10/2022
6	15/09/2022
7	19/09/2022
8	23/09/2022
9	26/09/2022
10	3/10/2022
11	13/09/2022
12	12/09/2022
13	21/09/2022
14	2/10/2022
15	24/09/2022
16	22/09/2022
17	16/09/2022
18	28/09/2022
19	26/09/2022
20	2/10/2022
Last 7 Days

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	=A1=MEDIAN(A1,TODAY(),TODAY()-7)	text	NO

 

[JamieP89]

Sorry for the delay in coming back I've been unwell!

Here's some data mocked up for demonstration purposes. So lets say I currently have the last 7 days being tracked (assuming its the 7th Sept today) - What I would like to be able to do, is conditionally format so that the highest and lowest number for the current rolling 7 days is just automatically tracking instead of having to shift the range for the conditional format each day.

So Tomorrow (F17 - 8th Sept for these purposes) would now be in range, and F10 (1st) would be dropped. Just finding it hassle to have to change every day (not to mention there's margin for error in forgetting!)

	Numbers
01-Sep-22	88
02-Sep-22	82
03-Sep-22	26
04-Sep-22	11
05-Sep-22	15
06-Sep-22	9
07-Sep-22	6
08-Sep-22
09-Sep-22
10-Sep-22
11-Sep-22
12-Sep-22
13-Sep-22
14-Sep-22
15-Sep-22
16-Sep-22
17-Sep-22
18-Sep-22
19-Sep-22
20-Sep-22
21-Sep-22
22-Sep-22
23-Sep-22
24-Sep-22
25-Sep-22
26-Sep-22
27-Sep-22
28-Sep-22
29-Sep-22
30-Sep-22
01-Oct-22

 

[Peter_SSs]

It's 23 September here at the moment

22 09 23.xlsm
	A	B
1		Numbers
2	1-Sep-22	88
3	2-Sep-22	82
4	3-Sep-22	26
5	4-Sep-22	11
6	5-Sep-22	15
7	6-Sep-22	9
8	7-Sep-22	6
9	8-Sep-22
10	9-Sep-22
11	10-Sep-22
12	11-Sep-22
13	12-Sep-22
14	13-Sep-22
15	14-Sep-22
16	15-Sep-22
17	16-Sep-22
18	17-Sep-22
19	18-Sep-22
20	19-Sep-22
21	20-Sep-22
22	21-Sep-22
23	22-Sep-22
24	23-Sep-22
25	24-Sep-22
26	25-Sep-22
27	26-Sep-22
28	27-Sep-22
29	28-Sep-22
30	29-Sep-22
31	30-Sep-22
32	1-Oct-22
CF Week

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B32	Expression	=A2=TODAY()-6	text	NO
B2:B32	Expression	=A2=TODAY()	text	NO

 

[JamieP89]

It's 23 September here at the moment

22 09 23.xlsm
	A	B
1		Numbers
2	1-Sep-22	88
3	2-Sep-22	82
4	3-Sep-22	26
5	4-Sep-22	11
6	5-Sep-22	15
7	6-Sep-22	9
8	7-Sep-22	6
9	8-Sep-22
10	9-Sep-22
11	10-Sep-22
12	11-Sep-22
13	12-Sep-22
14	13-Sep-22
15	14-Sep-22
16	15-Sep-22
17	16-Sep-22
18	17-Sep-22
19	18-Sep-22
20	19-Sep-22
21	20-Sep-22
22	21-Sep-22
23	22-Sep-22
24	23-Sep-22
25	24-Sep-22
26	25-Sep-22
27	26-Sep-22
28	27-Sep-22
29	28-Sep-22
30	29-Sep-22
31	30-Sep-22
32	1-Oct-22
CF Week

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B32	Expression	=A2=TODAY()-6	text	NO
B2:B32	Expression	=A2=TODAY()	text	NO

Thank you - But I'm not sure this answers my original question. I am looking for the conditional format to highlight the min and max values over the last 7 days so green would be high, red low. and only show that on the last 7 days.

Appreciate it's the 22nd (i'm not overly worried about that as this is just for demonstration purposes here) but for full completeness then. Here's data for the entire month up to todays date. Can we write something that allows the conditional format to

1) Only track the last 7 days
2) Highlight max value over the last 7 days in green
3) highlight min value over the last 7 days in red

Essentially when I log in tomorrow to the 24th (23rd today) to a blank cell, I want to be able to fill that blank in, in the knowledge that the conditional format is already tracking the last 7 days based on TODAY (being the day I would be on)

	Numbers
01-Sep-22	9
02-Sep-22	27
03-Sep-22	43
04-Sep-22	20
05-Sep-22	27
06-Sep-22	57
07-Sep-22	15
08-Sep-22	73
09-Sep-22	17
10-Sep-22	44
11-Sep-22	56
12-Sep-22	13
13-Sep-22	14
14-Sep-22	53
15-Sep-22	73
16-Sep-22	91
17-Sep-22	25
18-Sep-22	94
19-Sep-22	72
20-Sep-22	89
21-Sep-22	34
22-Sep-22	22
23-Sep-22	27
24-Sep-22
25-Sep-22
26-Sep-22
27-Sep-22
28-Sep-22
29-Sep-22
30-Sep-22
01-Oct-22

 

[Peter_SSs]

Sorry, I thought that you were referring to the dates.

Try this instead

22 09 23.xlsm
	A	B
1		Numbers
2	1-Sep-22	88
3	2-Sep-22	82
4	3-Sep-22	26
5	4-Sep-22	11
6	5-Sep-22	15
7	6-Sep-22	9
8	7-Sep-22	6
9	8-Sep-22	88
10	9-Sep-22	82
11	10-Sep-22	88
12	11-Sep-22	82
13	12-Sep-22	26
14	13-Sep-22	11
15	14-Sep-22	15
16	15-Sep-22	9
17	16-Sep-22	6
18	17-Sep-22	88
19	18-Sep-22	82
20	19-Sep-22	26
21	20-Sep-22	11
22	21-Sep-22	15
23	22-Sep-22	9
24	23-Sep-22	6
25	24-Sep-22	88
26	25-Sep-22	82
27	26-Sep-22	26
28	27-Sep-22	11
29	28-Sep-22	15
30	29-Sep-22	9
31	30-Sep-22	6
32	1-Oct-22
CF Week

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B32	Expression	=AND(A2<=TODAY(),A2>TODAY()-7,B2=MAXIFS(B$2:B$32,A$2:A$32,"<="&TODAY(),A$2:A$32,">"&TODAY()-7))	text	NO
B2:B32	Expression	=AND(A2<=TODAY(),A2>TODAY()-7,B2=MINIFS(B$2:B$32,A$2:A$32,"<="&TODAY(),A$2:A$32,">"&TODAY()-7))	text	NO

 

[JamieP89]

Sorry, I thought that you were referring to the dates.

Try this instead

22 09 23.xlsm
	A	B
1		Numbers
2	1-Sep-22	88
3	2-Sep-22	82
4	3-Sep-22	26
5	4-Sep-22	11
6	5-Sep-22	15
7	6-Sep-22	9
8	7-Sep-22	6
9	8-Sep-22	88
10	9-Sep-22	82
11	10-Sep-22	88
12	11-Sep-22	82
13	12-Sep-22	26
14	13-Sep-22	11
15	14-Sep-22	15
16	15-Sep-22	9
17	16-Sep-22	6
18	17-Sep-22	88
19	18-Sep-22	82
20	19-Sep-22	26
21	20-Sep-22	11
22	21-Sep-22	15
23	22-Sep-22	9
24	23-Sep-22	6
25	24-Sep-22	88
26	25-Sep-22	82
27	26-Sep-22	26
28	27-Sep-22	11
29	28-Sep-22	15
30	29-Sep-22	9
31	30-Sep-22	6
32	1-Oct-22
CF Week

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B32	Expression	=AND(A2<=TODAY(),A2>TODAY()-7,B2=MAXIFS(B$2:B$32,A$2:A$32,"<="&TODAY(),A$2:A$32,">"&TODAY()-7))	text	NO
B2:B32	Expression	=AND(A2<=TODAY(),A2>TODAY()-7,B2=MINIFS(B$2:B$32,A$2:A$32,"<="&TODAY(),A$2:A$32,">"&TODAY()-7))	text	NO

This worked a treat - Just wish I could fully understand all of it

 

[Peter_SSs]

Glad it worked for you. Thanks for letting us know.

 

[JamieP89]

Glad it worked for you. Thanks for letting us know.

Hope you don't mind me reopening this to ask, but I'm trying to understand the importance of AND and how this is used within the overall formula? I've read up and understand this is for checking whether more than one piece of logic is TRUE or if not FALSE, but can't understand why that would be important?

Also, while I am asking

Why do we write the remaining piece of the formula as below? Sorry, my need to understand everything about this is too much to leave alone and just accept it works

B2=MAXIFS(B$2:B$32,A$2:A$32,"<="&TODAY(),A$2:A$32,">"&TODAY()-7))

 

[Peter_SSs]

Hope you don't mind me reopening this to ask,

Not at all. It is good that you are trying to 'understand', rather than just 'use'.
I'll do the best I can to explain, by looking at the green (max) condition

Let's start at the end
B2=MAXIFS(B$2:B$32,A$2:A$32,"<="&TODAY(),A$2:A$32,">"&TODAY()-7)

The amber part calculates the maximum value in column B for dates in the last week (88). So, to be green a cell in column B must contain 88. However, as you can see, there are five 88 values in column B so being 88 is not enough by itself. We have to find the particular 88 (or there could be more than one of them) that has a date in the last week. Excel does not have a function for "the last week" so we get to the final result by saying that to be green ..

• the column B value must be the greatest value in the last week (88), AND
• the date in column A must be today's date or less, AND
• the date in column A must be greater than 7 days ago

Those last two points together create the "in the last week" condition.

Our formula has those three conditions but just in a different order, and the syntax is that the AND goes out the front rather than between each condition
=AND(A2<=TODAY(),A2>TODAY()-7,B2=MAXIFS(B$2:B$32,A$2:A$32,"<="&TODAY(),A$2:A$32,">"&TODAY()-7))

I hope that helps.