Conditional Formatting

JamieP89

Board Regular
Joined
Mar 8, 2022
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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?
 
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. :)

AH brilliant. Well this certainly goes some ways in helping to explain that, thank you! - Can I ask, is there any reason we reference A2 / B2 in the formula instead of saying A:A or B:B ? not sure I understand the significance of the A2 or B2 reference
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
In applying conditional Formatting it is common to first select the range that you want to apply the CF to. Having done that, any formula entered should relate to the 'active cell' which is generally the first cell in the selected range. For example,
In post #2 I selected A1:A2 so the entered formula was in reference to A1
In post #4 I selected B2:B32 so the formula entered was in reference to B2. The formula didn't mention B2 though as in deciding whether or not to format B2, that formula needed to look at A2
In post #6 I selected B2:B32 again so again the formula entered was in reference to B2.

When you do that, Excel automatically adjusts the formula to match the other rows in the range whenever a "$" does not "lock" the row number.
So, from post #6 the first formula, for row 2 is
=AND(A2<=TODAY(),A2>TODAY()-7,B2=MAXIFS(B$2:B$32,A$2:A$32,"<="&TODAY(),A$2:A$32,">"&TODAY()-7)

For row 3, Excel will apply this formula instead.
=AND(A3<=TODAY(),A3>TODAY()-7,B3=MAXIFS(B$2:B$32,A$2:A$32,"<="&TODAY(),A$2:A$32,">"&TODAY()-7)
The blue numbers have changed to refer to row 3 but the red numbers remain unchanged since the are 'absolute' row references due to the $ signs.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top