Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for extracting hour from Date string
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Numbers from string | Excel Worksheet Functions | |||
Extracting text from a string | Excel Programming | |||
Extracting a string | Excel Discussion (Misc queries) | |||
Extracting A Portion Of A String | Excel Programming | |||
Subtracting date:hour from date:hour | Excel Worksheet Functions |