Highlight row if cell contains text but not if date is entered (but generally formatted!)

[SaraWitch]

Hello peeps,

I have a column where a date is entered. However, I cannot format the cells as a date as text also need to be added to the same cell (a further column is not wanted!). Is there a way I can highlight the whole row if the cell contains text but not a date? All the (CF) formulas I know work around the cells being formatted as a date, so not sure it can be done...

Any help would be greatly appreciated!

 

[etaf]

how will the date be entered into the cell - first , anywhere
as far as excel is concerned its just text and so need some rules to identify - i guess
like looking for some / /
if its random, can you provide examples of how the cell will look

5/4/24 text 123 bla bla
05/04/24 text 123 bla bla
5th April 2024 text 123 bla bla
text 123 05/04/24 bla bla

 

[SaraWitch]

Ah, so I can conditionally format saying if text doesn't contain slashes, then highlight row...?

I've just tried:

=$S3<>"*/*"

But that's highlighting all cells...

And:

=COUNTIF($S2,"*/*")=0

But that's highlighting all cells (including those with dates, that are all formatted "dd/mm/yyyy"), except the "N/A" ones...

 

[etaf]

try

=AND(A2<>"",NOT(ISNUMBER(SEARCH("/",A2,1))))

columns D,E<F are not needed - just shows the evolution of formula

so change to your column S3 , if thats where it starts
=AND($S3<>"",NOT(ISNUMBER(SEARCH("/",$S3,1))))

Book5
	A	B	C	D	E
1				this will find /	finds without / and also ignore blank cells
2	123			FALSE	TRUE
3	12aaa4			FALSE	TRUE
4	abc			FALSE	TRUE
5	1/2/24 aaaa			TRUE	FALSE
6	bbb			FALSE	TRUE
7	01/02/2023 aaa			TRUE	FALSE
8				FALSE	FALSE
9				FALSE	FALSE
10				FALSE	FALSE
11				FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=ISNUMBER(SEARCH("/",A2,1))
E2:E11	E2	=AND(A2<>"",NOT(ISNUMBER(SEARCH("/",A2,1))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A14	Expression	=AND(A2<>"",NOT(ISNUMBER(SEARCH("/",A2,1))))	text	NO

 

[SaraWitch]

Nearly! But it's also highlighting where there are only dates for me...

¦ MrExcel Queries.xlsm
	A
1	Testing
2	02/04/2024
3	04/04/2024
4	N/A
5	Testing
6	05/04/2024 Testing
Highlight Row

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=AND(A1<>"",NOT(ISNUMBER(SEARCH("/",A1,1))))	text	NO

And, just thought, I also need the row highlighted if the cell is blank (but there is text in column A):

¦ MrExcel Queries.xlsm
	A	B	C	D	E
1	CF			Desired
2	Address 1	Testing		Address 1	Testing
3	Address 2	02/04/2024		Address 2	02/04/2024
4	Address 3	04/04/2024		Address 3	04/04/2024
5	Address 4	N/A		Address 4	N/A
6	Address 5	Testing		Address 5	Testing
7	Address 6	05/04/2024 Testing		Address 6	05/04/2024 Testing
8	Address 7	Testing 05/04/2024		Address 7	Testing 05/04/2024
9	Address 8			Address 8
10
Highlight Row

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B12	Expression	=AND($B2<>"",NOT(ISNUMBER(SEARCH("/",$B2,1))))	text	NO

 

[etaf]

yep, because it will NOT have a / thats a REAL date
which is a number
and excel puts a MASK over the number to show as a date when the cell is formatted as a date - or they just enter a date and excel will automatically format as a date

4/4/24 is 45386

so we could add an OR to exclude number above xxx

BUT will people just enter a number like
45386
and it means a number NOT a date

excel adds a 1 for every day

so next year would be something like 45386 + 365
45751

 

[SaraWitch]

Ah, brilliant - so I've formatted the cells as 'Text' and it works!!! Fantastic! Thank you so much, @etaf - wouldn't have got there on my own!

(I tweaked the formula to also highlight blanks if text in first column.)

¦ MrExcel Queries.xlsm
	A	B	C	D	E
1	CF			Desired
2	Address 1	Testing		Address 1	Testing
3	Address 2	02/04/2024		Address 2	02/04/2024
4	Address 3	04/04/2024		Address 3	04/04/2024
5	Address 4	N/A		Address 4	N/A
6	Address 5	Testing		Address 5	Testing
7	Address 6	05/04/2024 Testing		Address 6	05/04/2024 Testing
8	Address 7	Testing 05/04/2024		Address 7	Testing 05/04/2024
9	Address 8			Address 8
10
11
12
Highlight Row

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B12	Expression	=AND($A2<>"",NOT(ISNUMBER(SEARCH("/",$B2,1))))	text	NO

 

[etaf]

you are welcome