How to determine format of cell value 1

[MarkWittie]

I need to be able to tell if a particular cell contains text or a valid date before using the DATEDIFF function. I can't find a property for the RANGE object that tells you the cell format. Anybody have a suggestion?

Mark

 

[xlbo]

it's the NUMBERFORMAT property of the range object that you require but why bother ??
Just use format(Range.value,"dd/mm/yy&quot
to make sure that they are dates Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[MarkWittie]

I figured it out:

If Vartype(Cells(10,10).Value) = vbDate Then
....
End If

Thanks for the reply.

Mark

 

[RobBroekhuis]

Cool. I've been using typename() forever, but had never come across vartype(). I suspect they have equivalent functionality, but it's good to know they both exist...
Excel is quite sneaky in assigning variable type to cells. I think any time d,m,y,h,m or s appears in the numberformat, it's automatically a "date". Still annoys the heck out of me how, anytime I do a calculation involving manipulation of time, Excel automatically makes the result a time value.
Rob