Elegant way to convert table of times to 24-hour format?

[Three Lefts]

I have a rather large table one column of which contains times in
12-hour format without the colon: 642a, 1009p, etc. (no seconds)

Can anyone think of a clever way to convert that column to standard
24-hour format: 06:42:00, 20:09:00, etc.?

 

[macropod]

Hi Three Lefts,

Here's a macro to do the job:
Sub TimeConvert()
Dim i As Integer
Dim j As Integer
Dim strTxt As String
If Selection.Information(wdWithInTable) = True Then
With Selection.Tables(1)
j = 1
For i = 1 To .Rows.Count
If Len(.Cell(i, j).Range.Text) <> 2 Then
strTxt = Left(.Cell(i, j).Range.Text, Len(.Cell(i, j).Range.Text) - 2)
If Len(strTxt) = 4 Then strTxt = "0" & strTxt
If Left(strTxt, 2) = "12" Then strTxt = "00" & Right(strTxt, 3)
If Right(strTxt, 1) = "p" Then
strTxt = Left(Replace(strTxt, Left(strTxt, 2), Left(strTxt, 2) + 12 & ":"), 5)
Else
strTxt = Left(Replace(strTxt, Left(strTxt, 2), Left(strTxt, 2) & ":"), 5)
End If
strTxt = strTxt & ":00"
.Cell(i, j).Range.Text = strTxt
End If
Next
End With
End If
End Sub

As coded, the macro works on the first column in the table. To change the column, change 'j = 1' to 'j = #' where # is the column
number.

To update the table, simply select anywhere within the table, press Alt-F8 and choose the 'TimeConvert' macro.

Note: There's no error checking in the code so, if you've got anything else in the column, it's liable to get munged.

Cheers

 

[Three Lefts]

Hi Three Lefts,

Here's a macro to do the job:
Sub TimeConvert()
Dim i As Integer
Dim j As Integer
Dim strTxt As String
If Selection.Information(wdWithInTable) = True Then
With Selection.Tables(1)
j = 1
For i = 1 To .Rows.Count
If Len(.Cell(i, j).Range.Text) <> 2 Then
strTxt = Left(.Cell(i, j).Range.Text, Len(.Cell(i, j).Range.Text) - 2)
If Len(strTxt) = 4 Then strTxt = "0" & strTxt
If Left(strTxt, 2) = "12" Then strTxt = "00" & Right(strTxt, 3)
If Right(strTxt, 1) = "p" Then
strTxt = Left(Replace(strTxt, Left(strTxt, 2), Left(strTxt, 2) + 12 & ":"), 5)
Else
strTxt = Left(Replace(strTxt, Left(strTxt, 2), Left(strTxt, 2) & ":"), 5)
End If
strTxt = strTxt & ":00"
.Cell(i, j).Range.Text = strTxt
End If
Next
End With
End If
End Sub

As coded, the macro works on the first column in the table. To change the column, change 'j = 1' to 'j = #' where # is the column
number.

To update the table, simply select anywhere within the table, press Alt-F8 and choose the 'TimeConvert' macro.

Note: There's no error checking in the code so, if you've got anything else in the column, it's liable to get munged.

Cheers

Works like a charm. Thanks a bunch. That saved me a lot of time, and I
learned something to boot. Where do I send the beer? ;-)