Condition formating with a lot of dates?

[Garfield10]

Hello,
I hope this doesn't sound confusing, but here's my issue.
I have a big database structured like this:

In our country, we as a company are forced to test for COVID at least once a week. We have over 1000 employees and you can imagine this would be hell to keep track of manually.
I need Excel to check every name and their respective dates of tests and color the 2 columns appropriately.

I managed to make a formula to check if employee has been tested within past 7 days, but I can't make the calendar week work...

Any help MUCH appreciated!

 

[Joe4]

Could it be the cause that I don't know how to first select the area and then apply CF to the B and C column?

How do you go to any cell in Excel? Move your mouse and click on it, right?
That is all that I mean. And you can select a whole range of cells simply by clicking your mouse and dragging down the column.

Either way, another person responded to me on another forum (I posted here and on the other one in hopes at least someone would help)

That is know as Cross-Posting. While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

So please post the link to that thread on the other forum here.
And also note that most forums have similar policies, so you probably want to be sure to do the same on all your forums (some forums get quite upset if you Cross-Post without posting links).

 

[Garfield10]

How do you go to any cell in Excel? Move your mouse and click on it, right?
That is all that I mean. And you can select a whole range of cells simply by clicking your mouse and dragging down the column.

That is know as Cross-Posting. While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

So please post the link to that thread on the other forum here.
And also note that most forums have similar policies, so you probably want to be sure to do the same on all your forums (some forums get quite upset if you Cross-Post without posting links).

haha yes, I know how to select the area of cells, my issue was what exactly to do after doing that.
1. - I select the range of dates
2. - Then when I click on CF and create a new rule it just automatically applies it to the selected area, there's nowhere to select either the B or C cells.

I'm probably just being dumb but I honestly don't know. I've been trying for quite a while now.

And I apologize about cross-posting, in fact that's the first time I've heard about it as I don't use forums that much and I wasn't aware it wasn't appropriate
The link to the other post is:

https://www.reddit.com/r/excel/comments/mrctqi

 

[Joe4]

haha yes, I know how to select the area of cells, my issue was what exactly to do after doing that.
1. - I select the range of dates
2. - Then when I click on CF and create a new rule it just automatically applies it to the selected area, there's nowhere to select either the B or C cells.

I'm probably just being dumb but I honestly don't know. I've been trying for quite a while now.

You are overthinking this.

You have two rules, right? One for "last week", and one for "this week".
You do not select which cells to apply this to while doing the Conditional Formatting formula, you do it BEFORE you enter Conditiona Formatting.

So, for the "last week" rule, let's say the cells that you want "potentially" colored (if it meets the criteria) are the cells B2:B100.
Then you follow these steps:
1. Select cells B2:B100
2. Go to Conditional Formatting, and select the "Use a formula to determine which cells to format" option.
3. Enter the formula, as it pertains to the very first cell in the range you selected up in step 1 (so, B2 in our example).
You do not need to worry about adjusting the formula for the other cells, Excel will take care of that for you, just like if you copy a formula down a column, you do not need to edit each formula to increment any row references, Excel does it for you.
4. Choose your desired formatting option.
5. Click OK.

Then you would repeat the same steps for the "this week" rule to apply to column C (so selecting C2:C100 and using C2 in your formula).

 

[Garfield10]

You are overthinking this.

You have two rules, right? One for "last week", and one for "this week".
You do not select which cells to apply this to while doing the Conditional Formatting formula, you do it BEFORE you enter Conditiona Formatting.

So, for the "last week" rule, let's say the cells that you want "potentially" colored (if it meets the criteria) are the cells B2:B100.
Then you follow these steps:
1. Select cells B2:B100
2. Go to Conditional Formatting, and select the "Use a formula to determine which cells to format" option.
3. Enter the formula, as it pertains to the very first cell in the range you selected up in step 1 (so, B2 in our example).
You do not need to worry about adjusting the formula for the other cells, Excel will take care of that for you, just like if you copy a formula down a column, you do not need to edit each formula to increment any row references, Excel does it for you.
4. Choose your desired formatting option.
5. Click OK.

Then you would repeat the same steps for the "this week" rule to apply to column C (so selecting C2:C100 and using C2 in your formula).

Thank you.

It finally clicked for me, I understood the instructions a bit differently at first...

Once again, thank you very much for taking your time to help me, I really appreciate it!

Should I somehow close this topic as Solved?

 

[Joe4]

Should I somehow close this topic as Solved?

Sure. You would just check quick on the post that contains the solution.