Conditional formatting and multiple formulas

Hi everyone, first post!
I'm somewhat of a novice at excel and haven't quite dabbled in multiple formulas yet..

I'm trying to get a date to highlight read when a cell contains one of 2 pieces of text and is over 7 days

From my fiddling I was considering using a combination of
=IF(ISNUMBER(SEARCH("Data Requested",B3)) [But I need to have this text or "Partial Data Received"]
and
=IF(B3>=TODAY()-7)

and then input this into the conditional formatting section. However I'm not sure how to get them to work together.

excel.png

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number like 2008) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

For a CF rule, you could try this (untested as no workbook provided):

=AND(B3>=TODAY()-7,OR(ISNUMBER(SEARCH("Data Requested",B3)),ISNUMBER(SEARCH("Partial Data Received",B3))))

Untested, but try:
=AND(B3>=TODAY()-7, OR(ISNUMBER(SEARCH("Data Requested",B3)),
ISNUMBER(SEARCH("Partial Data Received",B3))))

What you really need, then, is this:

=OR(B2 < TODAY()-7,ISNUMBER(SEARCH("Data Requested",B2)),ISNUMBER(SEARCH("Partial Data Received",B2)))

Your profile still says 2008 - that isn't right, is it???

That still returns the same result.

OK, then you need to explain your logic clearly.

There is clearly more to it than what you ahev told us. Please talk us through those rows you have highlighted.

So the ones I've highlighted need to be false because they are within the 7 days. Its only dates over 7 days that I need Data Received or Partial Data Received to be searched and return True

Sorry! Silly mistake by me!

=AND(A2>=TODAY()-7,OR(ISNUMBER(SEARCH("Data Requested",B2)),ISNUMBER(SEARCH("Partial Data Received",B2))))

Which version of Excel are you using?

Please attach the workbook where you have tried to add the CF and I will troubleshoot it for you.

Based on what you have said, this should work:

=AND(A15 < TODAY()-7),OR(ISNUMBER(SEARCH("Data Requested",B15)),ISNUMBER(SEARCH("Partial Data Received",B15))))

Regarding your profile, you CANNOT be using the ANCIENT Excel 2008, can you? Don't even think there was a version of that name.

Look:

Maybe https://www.macworld.com/article/188874/excel2008.html

LOL! I do hope not, Trevor - but thanks for the link.

The CF formula works fine, as far as I can see. Maybe just that the current date has moved on since you created, tested and posted the sample workbook?

Oh sorry yes its 365

As explianed in post #2, 2008 in this case is a release number. Your version is 365 - please update your profile.

1. Please update your profile!!!
2. Please attach the workbook (NOT a picture of it).

Look at the update example with Ali's formula both in the sheet AND in the CF.

CF rule for A2:

=AND($A2 < TODAY()-7,OR(ISNUMBER(SEARCH("Data Requested",$B2)),ISNUMBER(SEARCH("Partial Data Received",$B2))))

Applies to:

=$A$2:$B$15

Do NOT use full column references - it will not work.

AliGW on MS365 Insider (Windows) 64 bit

	A	B	C
1	DATE REQUESTED	STATUS	Should be True
2	19/02/2022	Other	N
3	20/02/2022	Data Requested	Y
4	21/02/2022	Data Requested	Y
5	22/02/2022	Other	N
6	23/02/2022	Data Requested	Y
7	24/02/2022	Partial Data Received	Y
8	25/02/2022	Data Requested	N
9	26/02/2022	Data Requested	N
10	27/02/2022	Other	N
11	28/02/2022	Data Requested	N
12	01/03/2022	Data Requested	N
13	02/03/2022	Other	N
14	03/03/2022	Data Requested	N
15	04/03/2022	Data Requested	N

Sheet: Sheet2

BUT, if you do use full column references, the formula needs to refer to row 1

Formula:

Please Login or Register  to view this content.

I attached an extra picture ...

Please don't attach screenshots - we can't troubleshoot them. Attach WORKBOOKS!

I have shown you how to do it in post #25 above.

@Trevor - yes, the references could be set to $A1 and $B1, but A1 would need to be selected when you set the rule, otherwise weird things can happen!

@Ali: just explaining to the OP that if the range is A:A, the formula must relate to row 1. I think, by implication, if you have selected the entire column, it will apply to row 1.

Well, you have two examples. What's different between them and yours?

@Trevor - if you set the formula at $A2 and then choose $A:$A in the applies to range, everything will be OFSET by one row.

@PHBAYFORD

Once again, attach the WORKBOOK so that we can troubleshoot what you have done. Or are you saying that you are unable to enter a CF formula?

What, PRECISELY, is NOT WORKING?

No, you are incorrect - I am capitalising the key words so that you know what information we need to help you. Apologies if you feel I am being aggressive, but I have asked to see your workbook four times now, and still you have not provided it. If you do, I (or someone else) cam tell you what you have done wrong. What is stopping you from providing it?

My only wish is to help you resolve this without going round in circles (which is what we are doing now because you haven't attached the workbook).

It has been three and a half hours now - we could have had this sorted in ten minutes.

1. Select cell A2.
2. Open the CF formula dialog and use this CF rule: =AND($A2 < TODAY()-7,OR(ISNUMBER(SEARCH("Data Requested",$B2)),ISNUMBER(SEARCH("Partial Data Received",$B2)))) - the formula you have used is referencing the wrong row and does not have the anchored cells ($A2 and $B2).
3. In the Applies to box, type your range (e.g. =A$2:B$15).

@Ali:

@Trevor - if you set the formula at $A2 and then choose $A:$A in the applies to range, everything will be OFSET by one row.

I wouldn't do it that way. I'd select column A, then select CF, and then put the formula in, etc.

That's the other way.

@PHBAYFORD

Still don't know why mine wasn't

I have tried to explain again in post #36. Let me know if this makes any more sense to you.

So to apply it to a whole column I need to use. =$A:A if I change the formula to A1 instead of A2?

Yes, but before you do, delete the other rule and select cell A1 when you apply the rule instead of A2.

To be clear, the formula must be this:

=AND($A1 < TODAY()-7,OR(ISNUMBER(SEARCH("Data Requested",$B1)),ISNUMBER(SEARCH("Partial Data Received",$B1))))

And the applies to range this:

=$A:$B

OK. The first thing to check is that all of your dates are real dates.

1. Select the column containing the dates.
2. Change the formatting of the column from Date to Number or General.

Do ALL of the dates change to a 5-digit serial number?

Great! You are welcome, and please understand that sometimes we have to emphasise things to get things across.

I had not seen your updated workbook - I was expecting you to attach it to a new post, not to the original post.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.