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

--
Message posted from http://www.ExcelForum.com

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


"soccerstr1 " wrote in message
...
i have a spreadsheet with different arrival and departure times for an
airline. the times are entered in military time, and i was wondering if
there is a way to automatically convert the military time without a
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 have
tried using the following vba script, but i have to double click on
each cell individually.

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"soccerstr1 " wrote in message
...
i have a spreadsheet with different arrival and departure times for an
airline. the times are entered in military time, and i was wondering if
there is a way to automatically convert the military time without a
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 have
tried using the following vba script, but i have to double click on
each cell individually.

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

--
Message posted from http://www.ExcelForum.com

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 " wrote in message
...
ok, not to sound dumb, but where would i paste this sub? not to familiar
with macros


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com