long dates not recognized as dates

[rjmdc]

ji
my date column has dates as long Dtaes december 24, 2024
the cells list cell types as date however i cannot sort or filetr as they eem to be text lso verified by formula =ISNUMBER(DATEVALUE(A2))
most say text

what can I do to fix this?
thanks

 

[rjmdc]

is number all say true
so now how do i make it a date?

 

[Cubist]

is number all say true

That means they're already dates.
DATEVALUE expects a string, but when you feed it a number it returns an error. It follows that ISNUMBER returns FALSE.

 

[rjmdc]

these are not

Sample log.xlsx
	B
10	December 24, 2024
11	December 24, 2024
12	December 24, 2024
13	December 24, 2024
14	December 24, 2024
Log (2)

 

[Cubist]

Can you also show the =ISNUMBER for these cells? You said they all returned TRUE.

 

[rjmdc]

they are all enterd the smae way one is a date the other not

Sample log.xlsx
	B	C	D	E	F	G	H
8	December 24, 2024					TRUE	FALSE
9	December 24, 2024					TRUE	FALSE
10	December 24, 2024						TRUE
11	December 24, 2024						FALSE
12	December 24, 2024						FALSE
13	December 24, 2024						FALSE
14	December 24, 2024						FALSE
15	December 24, 2024						FALSE
16	December 24, 2024						FALSE
17	February 6, 2019					FALSE	TRUE
Log (2)

Cell Formulas
Range	Formula
G8:G9,G17	G8	=ISNUMBER(DATEVALUE(B8))
H8:H17	H8	=ISNUMBER(B8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G3070	Expression	=ISBLANK(G2)=TRUE	text	YES
G2:G3070	Cell Value	<NOW()	text	NO

 

[Cubist]

You said they all returned TRUE for ISNUMBER but they're not? Any how, try this in col C.

Book1
	B	C	D	E	F	G	H
8	December 24, 2024	December 24, 2024					TRUE
9	December 24, 2024	December 24, 2024					TRUE
10	December 24, 2024	December 24, 2024					TRUE
11	December 24, 2024	December 24, 2024					TRUE
12	December 24, 2024	December 24, 2024					TRUE
13	December 24, 2024	December 24, 2024					TRUE
14	December 24, 2024	December 24, 2024					TRUE
15	December 24, 2024	December 24, 2024					TRUE
16	December 24, 2024	December 24, 2024					TRUE
17	February 06, 2019	February 06, 2019					TRUE
Sheet2

Cell Formulas
Range	Formula
C8:C17	C8	=--B8
H8:H17	H8	=ISNUMBER(C8)

 

[rjmdc]

in my table they are saying false
copy code down and see isnumber is false

 

[Cubist]

Can you show with XL2BB?

 

[rjmdc]

new table sample

Sample log.xlsx
	N	O
4	December 24, 2024	FALSE
5	December 24, 2024	TRUE
6	December 24, 2024	FALSE
7	December 24, 2024	FALSE
8	December 24, 2024	FALSE
9	December 24, 2024	FALSE
10	December 24, 2024	FALSE
11	December 24, 2024	FALSE
Log (2)

Cell Formulas
Range	Formula
O4:O11	O4	=ISNUMBER(N4)

 

[Cubist]

I showed you how to convert to dates in post 18...Use col O.

Book1
	N	O
4	24-Dec-24	24-Dec-24
5	45650	24-Dec-24
6	24-Dec-24	24-Dec-24
7	24-Dec-24	24-Dec-24
8	24-Dec-24	24-Dec-24
9	24-Dec-24	24-Dec-24
10	24-Dec-24	24-Dec-24
11	24-Dec-24	24-Dec-24
Sheet3

Cell Formulas
Range	Formula
O4:O11	O4	=--N4