CF : Check If Data as Real Date

[muhammad susanto]

hi all..

how to make conditional formatting with shading Red color for data is NOT a real date
my format date dd/mm/yyyy or dd-mmmm-yyyy
if blank data not highlighted red color too
for this :

Book1
	G	H
1	date	Explanation
2	31/12/2020	true format date
3	23/03/1989	true format date
4	12/12/2001	contains mark (') approstrop
5	24/13/2002	wrong format date
6	blank/empty cell
7	text	wrong format date
8	01/01/2005	true format date
Sheet1

any help, thanks in advance
.sst

 

[etaf]

ISNUMBER(G2)


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
G2:G100 - Change Reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=ISNUMBER(G2)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[muhammad susanto]

Hi.
Thank you so much!!!

 

[etaf]

you are welcome

 

[muhammad susanto]

hi ,i have use =isnumber(), but higlighted color in real date.
i want to if data not real date or wrong date, give me highligted red color

 

[etaf]

Sorry about that
try
=ISTEXT(G2)

Book1
	A	B	C	D	E	F	G	H	I
1							Dates
2							2/2/21
3							2/3/21
4							1/1/21
5							2/5/21
6							2/6/21
7							2/7/21
8							21/2/21
9							2/9/21
10							2/10/21
11							2/11/21
12							22/11/21
13							2/13/21
14							2/14/21
15							2/15/21
16							2/16/21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G16	Expression	=ISTEXT(G2)	text	NO

 

[muhammad susanto]

hi..
formula =istext()..working well but i want to add new criteria if cell blank/empty so highlighted too

 

[etaf]

OR( ISTEXT(G2),G2="")

Book1
	G
1	Dates
2	2/2/21
3	2/3/21
4	1/1/21
5	2/5/21
6
7	2/7/21
8	21/2/21
9	2/9/21
10	2/10/21
11	2/11/21
12	22/11/21
13	2/13/21
14	2/14/21
15
16	2/16/21
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G16	Expression	=OR( ISTEXT(G2),G2="")	text	NO

 

[muhammad susanto]

hi...i have trying for this but not work

=OR(ISTEXT(G4);G4="")

Book1
	G
4	TEXT
5	07/08/2021
6	BLANK CELL
7	0
8	5
Sheet1

note:
if format date or empty cell --->>do not highlighted red
how to modified?

 

[MARK858]

Book1
	G
4	TEXT
5	07/08/2021
6
7	0
8	5
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:G8	Expression	=NOT(LEFT(CELL("format",G4),1)="D")	text	NO

Wrong, see post number 12