Differentiate between Number and Date?

[Merle]

I'm trying to determine the type of information in each column. I was using
the TYPE function but it doesn't differentiate between a Number and a Date.
The dates in my cells are either:

3/21/1999
3/21/1999 20:31:56 PM

....both of which Excel reads as a Number (type=1)

I want, basically:

if(type(A1)=1,"N",if(type(A1)=2,"C"...etc., so that my result in the cell is
either N, D, C, or L

Any ideas?

Merle

 

[Merle]

Ah, and additionally (let's make things really complicated here), not all the
cells in a column are populated with data. So, I can't just test the first
cell in the column because it may be blank. I think it would be acceptable,
however, to find the first populated cell and test its TYPE.

Merle

 

[Dave O]

The nested IF looks like this. Please note you'll need to verify that
the letters for type(A1)=3 and type(a1)=4 are correct:
=IF(TYPE(A1)=1,"A",IF(TYPE(A1)=2,"C",IF(TYPE(A1)=3,"D",IF(TYPE(A1)=4,"L",""))))

 

[Merle]

Dave,

Thanks for the quick response!

Help>TYPE says my only options are:

Number = 1
Text = 2
Logical value = 4
Error value = 16
Array = 64

and, hence, I never get a 3, which in your statement would say it's a Date.
As I said, Dates appear to be the same type as Numbers. I understand this
and it makes sense given that what's behind the date is simply a number. But
I'd still like to be able to know which is which.

Merle

 

[Bob Phillips]

That's because there is no date type in Excel, dates are just numbers since
1st Jan 1900. You need a UDF, something like

Function CellType(rng As Range)
If rng.Count = 1 Then
If IsDate(rng) Then
CellType = "D"
ElseIf IsArray(rng) Then
CellType = "A"
ElseIf IsNumeric(rng) Then
CellType = "N"
ElseIf IsError(rng) Then
CellType = "E"
Else
CellType = "T"
End If
End If
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Merle via OfficeKB.com]

Thanks, Bob!

That's what I was afraid of - can't do it with a formula. Guess I'll have to
figure out how to define the function and use it.

Appreciate your help!

Merle

That's because there is no date type in Excel, dates are just numbers since
1st Jan 1900. You need a UDF, something like

Function CellType(rng As Range)
If rng.Count = 1 Then
If IsDate(rng) Then
CellType = "D"
ElseIf IsArray(rng) Then
CellType = "A"
ElseIf IsNumeric(rng) Then
CellType = "N"
ElseIf IsError(rng) Then
CellType = "E"
Else
CellType = "T"
End If
End If
End Function

I'm trying to determine the type of information in each column. I was using
the TYPE function but it doesn't differentiate between a Number and a Date.

[quoted text clipped - 13 lines]

Merle

 

[Merle via OfficeKB.com]

I've been able to do this successfully with the one exception of determining
whether a cell is TRUE/FALSE (and therefore for my purpose a Logical field).
In Help there's List of Worksheet Functions available to Visual Basic which
lists IsLogical but I get this error:

Sub or Function not defined

....when I try to use it.

I've also tried using

cell.Value(c) = "TRUE"
cell.Value(c) = 1
cell.Text(c) = "TRUE"

....neither of which work either as it evidently doesn't recognize what's in
that field as either one.

How does one test for a logical field?

Merle

That's because there is no date type in Excel, dates are just numbers since
1st Jan 1900. You need a UDF, something like

Function CellType(rng As Range)
If rng.Count = 1 Then
If IsDate(rng) Then
CellType = "D"
ElseIf IsArray(rng) Then
CellType = "A"
ElseIf IsNumeric(rng) Then
CellType = "N"
ElseIf IsError(rng) Then
CellType = "E"
Else
CellType = "T"
End If
End If
End Function

I'm trying to determine the type of information in each column. I was using
the TYPE function but it doesn't differentiate between a Number and a Date.

[quoted text clipped - 13 lines]

Merle

 

[Bob Phillips]

If cell.Value Then

I don't know what you mean by cell.Value(c), that is invalid syntax

--

HTH

RP
(remove nothere from the email address if mailing direct)

I've been able to do this successfully with the one exception of determining
whether a cell is TRUE/FALSE (and therefore for my purpose a Logical field).
In Help there's List of Worksheet Functions available to Visual Basic which
lists IsLogical but I get this error:

Sub or Function not defined

...when I try to use it.

I've also tried using

cell.Value(c) = "TRUE"
cell.Value(c) = 1
cell.Text(c) = "TRUE"

...neither of which work either as it evidently doesn't recognize what's in
that field as either one.

How does one test for a logical field?

Merle

That's because there is no date type in Excel, dates are just numbers since
1st Jan 1900. You need a UDF, something like

Function CellType(rng As Range)
If rng.Count = 1 Then
If IsDate(rng) Then
CellType = "D"
ElseIf IsArray(rng) Then
CellType = "A"
ElseIf IsNumeric(rng) Then
CellType = "N"
ElseIf IsError(rng) Then
CellType = "E"
Else
CellType = "T"
End If
End If
End Function

I'm trying to determine the type of information in each column. I was using
the TYPE function but it doesn't differentiate between a Number and a

Date.
[quoted text clipped - 13 lines]

Merle

 

[Merle via OfficeKB.com]

Ah, well, I guess I was trying to be both brief and not expose my elementary
programming (sigh). Here's the full code which might help you better help me.


Function CellType(rng As Range)

'get column and row from rng

col = rng.Column
n = rng.Row

'proceed down column until cell isn't empty
'or 100 rows, whichever comes first

Do While IsEmpty(Cells(n, col))
If n < 100 Then
Exit Do
End If
ActiveCell.Offset(1, 0).Select
n = n + 1
Loop

'now check the cell and test its data type
'display type like D for Date, L for Logical, etc.

cur = Cells(n, col)

If IsEmpty(cur) Then
CellType = "unknown"
ElseIf IsDate(cur) Then
CellType = "D"
ElseIf cell.Value(cur) = 1 Then
CellType = "L"
ElseIf IsNumeric(cur) Then
CellType = "N"
Else
CellType = "C"
End If

End Function

I've no doubt this isn't as efficient as it could be. I couldn't figure out
how else to traverse down the column and test each cell. Partially, I think,
because I couldn't get my head around what your "rng" was - cell or range?
If it's a range (which makes sense to me), how does one test each cell's
value's type?

So, this is where I ended up. My apologies for not posting it before.

Thanks!

Merle

If cell.Value Then

I don't know what you mean by cell.Value(c), that is invalid syntax

I've been able to do this successfully with the one exception of determining
whether a cell is TRUE/FALSE (and therefore for my purpose a Logical field).

[quoted text clipped - 42 lines]

 

[Bob Phillips]

Merle,

In a UDF you usually do just the single action. Your UDF might look at many
cells, but it returns just one result. You do NOT try and get a UDF to
return a result for the complete range (well, actually you can, but let's
keep it a bit simpler here).

So, in this case, I would not see a UDF going down a range of cells to work
out whether each is a formula, number or whatever, but rather to have a UDF
to check the first cell in your range, passing that cell as an argument to
the UDF, then the next and so on.

Thus, I see no merit in knowing here how to looping through the range, you
don't need to. Using the UDF I gave, you can put

=CellType(A1) in one cell, and
=CellType(A2) in another, each will be independently evaluated.

Does this make sense?

--

HTH

RP
(remove nothere from the email address if mailing direct)

Ah, well, I guess I was trying to be both brief and not expose my elementary
programming (sigh). Here's the full code which might help you better help me.


Function CellType(rng As Range)

'get column and row from rng

col = rng.Column
n = rng.Row

'proceed down column until cell isn't empty
'or 100 rows, whichever comes first

Do While IsEmpty(Cells(n, col))
If n < 100 Then
Exit Do
End If
ActiveCell.Offset(1, 0).Select
n = n + 1
Loop

'now check the cell and test its data type
'display type like D for Date, L for Logical, etc.

cur = Cells(n, col)

If IsEmpty(cur) Then
CellType = "unknown"
ElseIf IsDate(cur) Then
CellType = "D"
ElseIf cell.Value(cur) = 1 Then
CellType = "L"
ElseIf IsNumeric(cur) Then
CellType = "N"
Else
CellType = "C"
End If

End Function

I've no doubt this isn't as efficient as it could be. I couldn't figure out
how else to traverse down the column and test each cell. Partially, I think,
because I couldn't get my head around what your "rng" was - cell or range?
If it's a range (which makes sense to me), how does one test each cell's
value's type?

So, this is where I ended up. My apologies for not posting it before.

Thanks!

Merle

If cell.Value Then

I don't know what you mean by cell.Value(c), that is invalid syntax

I've been able to do this successfully with the one exception of determining
whether a cell is TRUE/FALSE (and therefore for my purpose a Logical

field).
[quoted text clipped - 42 lines]

Merle

 

[Merle via OfficeKB.com]

Bob,

I think the code I posted basically does what you describe in your second
paragraph - goes down through each cell, checks to see whether there's
anything in it, if so tests its value for formula, number, whatever.

I would put

=CellType(A4:A2000)

....at the top of every column I want to find what type of values are in that
column, changing the "A" to the appropriate column letter.

That part of the function works. What doesn't appear to work is the ability
to determine whether the cell's value is a logical (True/False) as opposed to
a numerical. Excel interprets the words "TRUE" and "FALSE" as numbers. I
can't figure out how to differentiate between the two.

Merle

Merle,

In a UDF you usually do just the single action. Your UDF might look at many
cells, but it returns just one result. You do NOT try and get a UDF to
return a result for the complete range (well, actually you can, but let's
keep it a bit simpler here).

So, in this case, I would not see a UDF going down a range of cells to work
out whether each is a formula, number or whatever, but rather to have a UDF
to check the first cell in your range, passing that cell as an argument to
the UDF, then the next and so on.

Thus, I see no merit in knowing here how to looping through the range, you
don't need to. Using the UDF I gave, you can put

=CellType(A1) in one cell, and
=CellType(A2) in another, each will be independently evaluated.

Does this make sense?

Ah, well, I guess I was trying to be both brief and not expose my elementary
programming (sigh). Here's the full code which might help you better help me.

[quoted text clipped - 57 lines]

 

[Bob Phillips]

You should test for Boolean before testing for numeric

If rng.Value = True Or rng.Value = False Then


--

HTH

RP
(remove nothere from the email address if mailing direct)

Bob,

I think the code I posted basically does what you describe in your second
paragraph - goes down through each cell, checks to see whether there's
anything in it, if so tests its value for formula, number, whatever.

I would put

=CellType(A4:A2000)

...at the top of every column I want to find what type of values are in that
column, changing the "A" to the appropriate column letter.

That part of the function works. What doesn't appear to work is the ability
to determine whether the cell's value is a logical (True/False) as opposed to
a numerical. Excel interprets the words "TRUE" and "FALSE" as numbers. I
can't figure out how to differentiate between the two.

Merle

Merle,

In a UDF you usually do just the single action. Your UDF might look at many
cells, but it returns just one result. You do NOT try and get a UDF to
return a result for the complete range (well, actually you can, but let's
keep it a bit simpler here).

So, in this case, I would not see a UDF going down a range of cells to work
out whether each is a formula, number or whatever, but rather to have a UDF
to check the first cell in your range, passing that cell as an argument to
the UDF, then the next and so on.

Thus, I see no merit in knowing here how to looping through the range, you
don't need to. Using the UDF I gave, you can put

=CellType(A1) in one cell, and
=CellType(A2) in another, each will be independently evaluated.

Does this make sense?

Ah, well, I guess I was trying to be both brief and not expose my elementary
programming (sigh). Here's the full code which might help you better

help me.
[quoted text clipped - 57 lines]

Merle