check if a cell has date or a number

M

mona

I am trying to type a formula where it will check if the cell to its right
is a date or a number, not the format of the cell to the right , but if it is
a date or a numeric number.
 
M

Mike H

A function maybe

Function isitadate(rng As Range)
isitadate = isdate(rng)
End Function

Put the function in a general module i.e. ALT+F11 to open VB editor, Right
click 'This Workbook' and Insert module and paste it in there.

call with
=isitadate(A1)

and it will return TRUE or FALSE

Mike
 
B

Bob Phillips

Assuming in A1

=ISNUMBER(B1)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
A

Arvi Laanemets

Hi

In Excel, a date IS a numeric number! Enter some date (format cell as date
before), and then change the format to general, and you'll see what I mean.
P.e. todays date is 39583 - enter this number into cell, and format it as
date to check this. (And 39583.5 is doday's midday)

So there is no way to differ between real numbers and dates in Excel, except
the number is negative or too big (the biggest possible date is 31. December
of 9999 = 2958465)
 
D

Dave Peterson

If you really want to distinguish between dates and numbers:

=AND(ISNUMBER(A1),LEFT(CELL("format",A1),1)="D")
 
F

FSt1

hi
arvi is right but you can use the cell function to check it's format as to
if it is formated as a date or as a number.
=cell(format, A2)
the cell function returns a code for the format. if formated to a standard
date ie
5/15/08, then the cell function returns D4. there are a number of codes
denoted the different formats. look up the cell function in xl help for a
list of all the codes.

Regards
FSt1
 
Top