Test cell content for data-type!! (Excel 2000>)

[Rudi]

Hi,
In VBA there is a function called ISDATE(). This is not available in Excel!!!

I have been playing with a spreadsheet to test for data-type. This query originated from a sheet containing a INDEX(MATCH(),MATCH()) type nested function to do a two way lookup of data on the sheet. Based on criteria, the resulting value returned could be a text value, or a Salary or a Date. I was trying to determine the type so I could format the cell as needed to show Salary ® or the dd-mmm-yyyy date format if a date!!

I have a measure of success, except for different date types!

Is there a better (more efficient way) of dealing with this~!!

Steve - How about a custom function!!! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

[HansV]

A very simple custom function:

Public Function IsADate(val) As Boolean
IsADate = IsDate(val)
End Function

Use like this:

=IsADate(A5)

or

=IF(IsADate(A5),"Yes! It's a date!","No date!")

[Rudi]

Tx!! (As simple as that eh!!)

I customised the rest of the function to do the job!!! It works like a dream!:
=IF(IsADate(C2),"Date",IF(AND(ISNUMBER(C2),NOT(IsA Date(C2))),"Salary","Text"))

Tx

[sdckapr]

One "ptential problem" is that a "date" is only a date if the cell is formatted as a date.

Any valid number in a cell formatted as a date will be considered a date. That same number in a cell not formatted as a date, will not be considered a date.

Steve

[Rudi]

Thats ok! Where I'm building the calc it will not have formatting!
Tx for the warning though!

[sdckapr]

<img src=/S/confused.gif border=0 alt=confused width=15 height=20>If it will not have formatting, it will not be a date...

Steve

[Rudi]

I see your point!

Can you help Steve!

[sdckapr]

How about in L4:
<pre>=IF(ISNUMBER(SEARCH("date",K3)),"Date",IF(K3= "Salary","Salary","Text"))</pre>


If you use in K4:

=TEXT(INDEX(A1:H22,MATCH(K2,A:A,0),MATCH(K3,1:1,0) ),IF(ISNUMBER(SEARCH("date",K3)),"m/d/yyyy",IF(K3="Salary","R #,##0","@")))

You can format the cell as desired....


Steve

[Rudi]

I see your approach now...

I think I got tunnel vision with my attempts to use the CELL() function to return details of the data type of a cell.
Thx for your help and solution!
Cheers

[sdckapr]

Glad I could help. The real "flaw" in your "logic" was that, unlike a program like access, a date in excel is just a number. Any number from 0 to 2958465 (12/31/9999) will be considered a date if the cell is formatted as a "date".
If the cell is formatted as date but contains text, an error, or a number is outside this range it is not a date.
If the cell is not formatted as a date, no matter what it contains, it is not a date.

My "logic" just used what the the column heading chosen was to define what the cell is.

[An alternate approach (perhaps more general) is to add a row and for each column, list what format you want to use. Then instead of the IF construction, read the "format" with the index/match or vlookup. Then you just have to change the "format text" in the table to redefine the display, without having to change your formula. It is also not limited to 7 different numeric formats as the IFs will be...]

[Rudi]

Tx Steve. Some nice advice in there!
Cheers for the solution!