How to check a cell value is a date ?

[L.Peter]

Dear Group,
how can i determine a cell value is date value? : i.e
3/1/2004 returns .t.
March 1st 2004 return .t. (this one is optional, would be nice if it
can be evaluated)
TIA

Peter

 

[Norman Harker]

Hi L.Peter!

You need a UDF

Function ISADATE(MyCell As Range)
ISADATE = IsDate(MyCell)
End Function

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Peter
you'll need VBA for this. You may try the following UDF
Function Is_Date(Cell As Range) as boolean
Is_Date = IsDate(Cell)
End Function

Use this function an a cell like
=Is_Date(A1)

 

[L.Peter]

Thanks Norman,
I was hoping to get a quick way to solve this problem, any way, it just
takes a bit longer as I have to do it manually

Best Regards

Peter

 

[L.Peter]

Thanks Frank,
It works

Best Regards

Peter

Hi Peter
you'll need VBA for this. You may try the following UDF
Function Is_Date(Cell As Range) as boolean
Is_Date = IsDate(Cell)
End Function

Use this function an a cell like
=Is_Date(A1)

 

[Harlan Grove]

how can i determine a cell value is date value? : i.e
3/1/2004 returns .t.
March 1st 2004 return .t. (this one is optional, would be nice if it
can be evaluated)

Can be done without udfs. If you want to check for both date values and text
strings that could be converted directly into date values, try

=((ISNUMBER(1/A1/(LEFT(CELL("Format",A1),1)="D"))+ISNUMBER(DATEVALUE(A1)))>0
)

For date values only, try

=ISNUMBER(1/A1/(LEFT(CELL("Format",A1),1)="D"))

 

[Frank Kabel]

Can be done without udfs. If you want to check for both date values
and text strings that could be converted directly into date values,
try

=((ISNUMBER(1/A1/(LEFT(CELL("Format",A1),1)="D"))+ISNUMBER(DATEVALUE(A1
)))>0
)

For date values only, try

=ISNUMBER(1/A1/(LEFT(CELL("Format",A1),1)="D"))

Hi Harlan
nice one!
Frank