Adding a colon to a time field if one doesn't exist.

[seh]

I have a gridview control with a date/time column. It's quite old and was free with a magazine but it does the job...almost. It won't let me format the column to just time. It has to be date and time. If the user puts in a time without the colon, e.g. 1730 instead of 17:30, the program crashes.

That's my scenario. What I need is a function that puts in a colon after the first two digits of a time field if they don't exist.

This would help me out considerably.

Thanks.

[phinds]

tstr = IIF (2 = instr(tstr, ":"), tstr, Left(tstr, 2) & ":" & right(tstr, len(tstr) - 2))

something like that

[andreys]

just check user input and display a message box:

if not IsDate(myTime) then
     MsgBox "Incorrect Time Format!"
Else
     do things....
End If

[IROY55]

Try this and let me know of it works for you:

Private Sub Command1_Click()
Dim strTime As String
 
    strTime = "1235"
    Debug.Print FormatTime(strTime)
 
End Sub
 
Public Function FormatTime(strValue As String) As String
'========================================================
Dim pos As Integer
 
    strValue = Trim(strValue)
    pos = InStr(1, strValue, ":")
    If pos = 0 Then
        If Len(strValue) = 4 Then
            FormatTime = Left(strValue, 2) & ":" & Right(strValue, 2)
        ElseIf Len(Trim(strValue)) = 6 Then
            FormatTime = Left(strValue, 2) & ":" & Mid(strValue, 3, 2) & ":" & Right(strValue, 2)
        End If
    End If
 
End Function

[IROY55]

This would be better to use:

If Len(strValue) = 4 Then
            FormatTime = Left(strValue, 2) & ":" & Right(strValue, 2)
        ElseIf Len(Trim(strValue)) = 6 Then
            FormatTime = Left(strValue, 2) & ":" & Mid(strValue, 3, 2) & ":" & Right(strValue, 2)
        Else
            MsgBox "Incorrect value, please re-enter", vbExclamation, "Format Time"
        End If

[andreys]

IROY55: How about 7:30 (730). It is a correct time.

[IROY55]

How about this:
If Len(strValue) = 3 Then strValue = "0" & strTime

As you understand this is only an example and NOT the final solution.

[Aaron Young]

If you're just dealing with a string representing time, how about:

sTime = Format(Replace(sTime, ":", ""), "00:00")

[andreys]

I agree. But still I think we should teach users to put correct values as an input and do not do THEIR work for them.

If len(strValue)<5 then: FormatTime = Left(strValue, Len(strValue)-2) & ":" & Right(strValue, 2)

[IROY55]

But still I think we should teach users to put correct values as an input and do not do THEIR work for them

Entirely dissagree. Just like in Retail: customer is always right! Same thing here: you are the one to blame for "incorrect" output - not them. You are the programmer - so build your program as flexble as possible.

[techgnome]

I half agree /w IRoy, and half agree w/ andreys..... We provide extensive training for our product to our users. We teach then how to put the data in correctly. We let them know that if they don't do it right... things could get pretty ugly..... I think the quote is: "Build an idiot proof program and they'll build a better idiot." -- trust me IT'S TRUE!!!! They STILL find ways around things..... This is why we have error handling AND we STILL have tons of code to do validations.... it's a real shame sometimes..... but some of these users are quite creative too....

[andreys]

Originally posted by andreys
just check user input and display a message box:

if not IsDate(myTime) then
     MsgBox "Incorrect Time Format!"
Else
     do things....
End If

This wont let user insert incorrect data, and in same time it will prompt to correct an input. This is flexible enought.
1. User always right.
2. If user not right, see #1.

[IROY55]

Andrey,
you must look at the problem much broader: you cannot (and must not) enforce any user to follow your rules as they will refuse to work with your product(s). This is a very common mistake many developers do and result may be catastrofic: you may loose your job or completely re-design your product. Besides, that snippet code you posted isn't that flexible at all. If user types something like "123456" which can be interpreted as "12:34:56" (a valid time BTW) isn't valid DATE, so your code will produce a MsgBox ... Think over and I'm sure you will agree with us.

[andreys]

I'm not trying to force user follow my rules, but (here is always but...) the element of education should be in place.

[IROY55]

That's not case, you are missing one main point: as techgnome said "Build an idiot proof program and they'll build a better idiot."

[seh]

Thanks for the snippets guys. It's helped me lots. I now have a function to validate my time fields. However it's not complete, as it doesn't take all input possibilities into acount. I'll have to add to it once users start to complain

Anyway, I thought it would be nice to post my results so far:

Public Function TimeCheck(ByVal strValue As String) As String
Dim FormatTime As String
Dim pos As Integer
    
    
    ' Check if the colon is already present. If so exit.
    If Len(strValue) = 4 Then
        pos = InStr(1, strValue, ":")
        If pos = 2 Then: TimeCheck = strValue: Exit Function
    Else
        If Len(strValue) = 5 Then
            pos = InStr(1, strValue, ":")
            If pos = 3 Then: TimeCheck = strValue: Exit Function
        End If
    End If
    

    
    If Len(Trim(strValue)) = 3 Then ' Three numbers have been entered.
        FormatTime = Left(strValue, 1) & ":" & Right(strValue, 2) ' Put colon after first digit.
        FormatTime = "0" & FormatTime ' Put in a zero before the first digit.
    ElseIf Len(strValue) = 4 Then   ' Four numbers have been entered.
        FormatTime = Left(strValue, 2) & ":" & Right(strValue, 2) ' Put colon after second digit.
    ElseIf Len(Trim(strValue)) = 6 Then  
        FormatTime = Left(strValue, 2) & ":" & Mid(strValue, 3, 2) & ":" & Right(strValue, 2)
    Else
        MsgBox "Incorrect value, please re-enter", vbExclamation, "Format Time"
    End If

    TimeCheck = FormatTime

End Function

Feel free to amend/suggest improvements
Thanks again.

Daz