Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
For my program, valid standard time formats look like this:
1:59 am 159am 159 AM 1:59 PM I created the following code to convert the above types of formats into military times. Does anyone know if this code can be improved or simplified? I prefer to do time conversions as quickly as possible. Maybe someone has a better technique than mine? Here is the code: '---------------------------------------------------------- Dim reg As New VBScript_RegExp_55.RegExp Dim sentence As String Dim tHours As String Dim tMins As String Dim tMeridiem As String Dim militaryHour As String Dim match, matches sentence = "6:59 pm" ' Example input time reg.Pattern = "^\s*(1[012]|[1-9]):?([0-5][0-9])\s*(am|pm)$" reg.IgnoreCase = True reg.Global = True ' Check if format is valid If reg.Test(sentence) Then Set matches = reg.Execute(sentence) Set match = matches(0) tHours = match.SubMatches(0) tMins = match.SubMatches(1) tMeridiem = match.SubMatches(2) If (tHours = "12") Then ' Handle midnight and noon cases If (tMeridiem = "am") Then militaryHour = "00" Else militaryHour = "12" End If Else If (tMeridiem = "am") Then militaryHour = tHours Else militaryHour = CStr(Val(tHours) + 12) End If If (Val(militaryHour) < 10) Then militaryHour = "0" + militaryHour End If End If MsgBox "Military time: " + militaryHour + tMins Else MsgBox "Invalid format time string" End If |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
Converting standard time to military time | Excel Worksheet Functions | |||
formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
How to I convert standard time to Military or 24 hour format? | Excel Discussion (Misc queries) | |||
Convert data into standard military time format | Excel Discussion (Misc queries) |