Hi
I have a column containing values that look like dates and times but
are not Excel dates:
eg:
* 4/27/2007 21:15 P.M
* 4/27/2007 9 P.M
(Some of the cells in the column are blank or have a "-" in them)

I need to extract the hour from this viz "21" and "9" in the examples
above . I wrote the following UDF. It works fine, except that #VALUE!
appears where the column does not have a date. How do I fix this?

Function gethour1(cell As Range) As String
Dim rspstring As String
Dim rspspace As Integer
Dim gethour As String
rspstring =
Application.WorksheetFunction.Trim(Application.Wor ksheetFunction.Substitute(cell,
Chr(160), ""))
rspspace = Application.WorksheetFunction.Find(" ", rspstring)
If IsNumeric(Mid(rspstring, rspspace + 2, 1)) Then gethour =
Mid(rspstring, rspspace + 1, 2) Else gethour = Mid(rspstring, rspspace
+ 1, 1)
On Error GoTo Errvalue:
gethour1 = CInt(gethour)
Exit Function
Errvalue:
gethour1 = "Not a date"
End Function

Thanks in advance for all the help.

Regards,
Raj

If len(cell.value)<6 then
gethour=""
exit function
else
v=trim(replace(cell.value,Chr(160),""))
t=Split(v," ")(1)
gethour=Split(t,":")(0)
end if

Assuming all of your values are either (almost) empty or comform to the
pattern you showed.

BTW it's more convenient to use the VBA
Trim()
Replace()
Instr()
directly instead of their worksheetfunction siblings.

Tim


"Raj" wrote in message
...
Hi
I have a column containing values that look like dates and times but
are not Excel dates:
eg:
4/27/2007 21:15 P.M
4/27/2007 9 P.M
(Some of the cells in the column are blank or have a "-" in them)

I need to extract the hour from this viz "21" and "9" in the examples
above . I wrote the following UDF. It works fine, except that #VALUE!
appears where the column does not have a date. How do I fix this?

Function gethour1(cell As Range) As String
Dim rspstring As String
Dim rspspace As Integer
Dim gethour As String
rspstring =
Application.WorksheetFunction.Trim(Application.Wor ksheetFunction.Substitute(cell,
Chr(160), ""))
rspspace = Application.WorksheetFunction.Find(" ", rspstring)
If IsNumeric(Mid(rspstring, rspspace + 2, 1)) Then gethour =
Mid(rspstring, rspspace + 1, 2) Else gethour = Mid(rspstring, rspspace
+ 1, 1)
On Error GoTo Errvalue:
gethour1 = CInt(gethour)
Exit Function
Errvalue:
gethour1 = "Not a date"
End Function

Thanks in advance for all the help.

Regards,
Raj

On Feb 27, 12:59*pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
If len(cell.value)<6 then
* * gethour=""
* * exit function
else
* * v=trim(replace(cell.value,Chr(160),""))
* * t=Split(v," ")(1)
* * gethour=Split(t,":")(0)
end if

Assuming all of your values are either (almost) empty or comform to the
pattern you showed.

BTW it's more convenient to use the VBA
* Trim()
* Replace()
* Instr()
directly instead of their worksheetfunction siblings.

Tim

"Raj" wrote in message

...
Hi
I have a column containing values that look like dates and times but
are not Excel dates:
eg:
4/27/2007 21:15 P.M
4/27/2007 9 P.M
(Some of the cells in the column are blank or have a "-" in them)

I need to extract the hour from this viz "21" and "9" in the examples
above . I wrote the following UDF. It works fine, except that #VALUE!
appears where the column does not have a date. How do I fix this?

Function gethour1(cell As Range) As String
Dim rspstring As String
Dim rspspace As Integer
Dim gethour As String
rspstring =
Application.WorksheetFunction.Trim(Application.Wor ksheetFunction.Substitute*(cell,
Chr(160), ""))
rspspace = Application.WorksheetFunction.Find(" ", rspstring)
If IsNumeric(Mid(rspstring, rspspace + 2, 1)) Then gethour =
Mid(rspstring, rspspace + 1, 2) Else gethour = Mid(rspstring, rspspace
+ 1, 1)
On Error GoTo Errvalue:
gethour1 = CInt(gethour)
Exit Function
Errvalue:
gethour1 = "Not a date"
End Function

Thanks in advance for all the help.

Regards,
Raj

Thanks Tim, It worked.

Raj