CODEWITHSUNDEEP
vbams-word
codiplex
codiplex

Reputation: 159

VBA: How to check if the time format is correct?

I want the macro to know if the time format is correct or not. For example, if the format that I want is "hh:mm:ss" and the value of Selection.Text is "01:10:20" then Msgbox True else if the value of Selection.Text is "01:20:0" or any value that is not match on the "hh:mm:ss" format then Msgbox False. Is there a way to do that?

Upvotes: 0

Views: 3295

Answers (2)

Paweł Jamiołkowski
Paweł Jamiołkowski

Reputation: 81

The previous answer is not correct because the numbers must be limited in ranges. The trick is to use the IsDate function, which should be added to the condition:

Dim ok As Boolean
With Selection
If IsDate(.Text) and .Text like "##:##:##" Then
Msgbox "ok"
Else
Msgbox "not ok"
End if
End With

However, in rare cases you may want to use other notations, such as 24:00 instead of 00:00 (see https://en.wikipedia.org/wiki/24-hour_clock). For special cases, it's best to use regexp. Below is the code for both 00 and 24 format:

Function IsTime(x)

Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")

regex.Pattern = "^([0-1][0-9])|(2[0-4]):[0-5][0-9]:[0-5][0-9]$"

IsTime = regex.test(x)

End Function


Sub test()

With Selection
If IsTime(.Text) Then
MsgBox "ok"
Else
MsgBox "not ok"
End If
End With

End Sub

Upvotes: 0

codiplex
codiplex

Reputation: 159

I found a solution to my question. I used the Like to check if the format is correct.

Dim ok As Boolean
With Selection
ok = .Text Like "##:##:##"
If ok = True Then
Msgbox ok
Else
Msgbox ok
End With
End With

Upvotes: 1

Related Questions