UDF for extracting hour from Date string

[Raj]

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.WorksheetFunction.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

 

[Tim Williams]

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


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.WorksheetFunction.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

 

[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


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.WorksheetFunction.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