problem with military time and making calculations

[soccerstr1]

i have a spreadsheet with different arrival and departure times for a
airline. the times are entered in military time, and i was wondering i
there is a way to automatically convert the military time without
colon into military time with a colon, or normal time with a colon
also, is it possible to do this when data is pasted into a cell? i hav
tried using the following vba script, but i have to double click o
each cell individually.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Su

 

[Bernie Deitrick]

Soccerstar,

Instead of the change event, try a bulk conversion. Select all the cells
you need to convert, and run the sub below.

HTH,
Bernie
MS Excel MVP

Sub ConvertToTimes()
Dim myCell As Range
Dim TimeStr As String

Application.EnableEvents = False
On Error GoTo Invalid
For Each myCell In Selection
With myCell
If myCell.Value <> "" Then
If Not .HasFormula Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "0" & .Value & ":00"
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = .Value & ":00"
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
..Value = TimeValue(TimeStr)
End If
End If
End With
Invalid:
Next myCell
Application.EnableEvents = True
End Sub

 

[Bernie Deitrick]

Sorry, I may have confused your intent: when you have a one or two digits, I
assumed hours. If you want the default to be minutes, change:

Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "0" & .Value & ":00"
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = .Value & ":00"

back to

Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "0:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "0:" & .Value

Sorry about that,
Bernie
MS Excel MVP

 

[soccerstr1]

ok, not to sound dumb, but where would i paste this sub? not to familia
with macro

 

[Bernie Deitrick]

Soccer Star,

Use Alt=F11 to go into the VBE (Visual Basic Editor), the use Ctrl-R to open
the project explorer, select your workbook, then use Insert | Module, and
paste the code into the big window that appears. Then you can go back to
Excel (alt-F11 again) and use Tools | Macro | Macros... then select "Macros
In.." and choose "This Workbook". Then select the macro and run it. You can
also assign the macro to a drawing object, or to a custom commandbar button.

HTH,
Bernie
MS Excel MVP

 

[soccerstr1]

ok, i tried running the macro and it didn't do anything. should thi
line have the periods in the beginning?

..Value = TimeValue(TimeStr)

i tried leaving them in, but i got an error message. i then removed th
periods and the macro was selected. i then creasted the cells i neede
to edit, and then ran the macro and nothing happened to the selecte
cells after i ran the macr

 

[Bernie Deitrick]

SoccerStar,

Send me a message privately and I will send you a working example workbook.

HTH,
Bernie
MS Excel MVP

 

[soccerstr1]

Bernie, i have sent a a pm. hope to hear from you soon. thanx agai

 

[Bernie Deitrick]

If you scatully sent it, I didn't get it: you need to take out the spaces
and change the dot to a "." in my email address.

Bernie

 

[Bernie Deitrick]

I _really_ need to work on my typing - - "If you _actually_ sent it......"

If you scatully sent it, I didn't get it: you need to take out the spaces
and change the dot to a "." in my email address.

Bernie

 

[soccerstr1]

bernie, i was just wondering if u have received my email i sen
yesterday

 

[Bernie Deitrick]

No. Enter this Excel formula in a cell:

="deitbe" & "@" & "consumer" & ".org"

Then press F2, select the whole formula, and press F9, then Ctrl-C to copy
my valid email address... ;-)

Bernie

 

[soccerstr1]

ok, now it has been sent